It surely does not feel like a full month and some more went by since we were enjoying PGCon 2010, but in fact it was already the time for CHAR(10). The venue was most excellent, as Oxford is a very beautiful city. Also, the college was like a city in the city, and having the accomodation all in there really smoothed it all. On a more technical viewpoint, the range of topics we talked about and the even broader one in the “Hall Track” make my mind full of ideas, again.

So, following previous blog entries about importing fixed width data, from Postgres Online Journal and David (perl) Fetter, I couldn’t resist following the meme and showing how to achieve the same thing with pgloader.

Yes. This pgloader project is still maintained and somewhat active. Development happens when I receive a complaint, either about a bug in existing code or a feature in yet-to-write code. If you have a bug to report, just send me an email!

This time we’re having a database where sequences were used, but not systematically as a default value of a given column. It’s mainly an historic bad idea, but you know the usual excuse with bad ideas and bad code: the first 6 months it’s experimental, after that it’s historic. *Not talking about genome orphaned sequences here, though* Still, here’s a query for 8.4 that will allow you to list those sequences you have that are not used as a default value in any of your tables:

Now you know what piece of software is used to publish this blog. I really like it, the major mode makes it a great experience to be using this tool, and the fact that you produce the HTML and rsync it all from within Emacs ( C-c C-p then C-c C-r with some easy elisp code) is a big advantage as far as I’m concerned. No need to resort to shell and Makefile.

So, if you followed the previous blog entry, now you have a new database containing all the static tables encoded in UTF-8 rather than SQL_ASCII. Because if it was not yet the case, you now severely distrust this non-encoding.

Now is the time to have a look at properly encoding the live data, those stored in tables that continue to receive write traffic. The idea is to use the UPDATE facilities of PostgreSQL to tweak the data, and too fix the applications so as not to continue inserting badly encoded strings in there.