subDimension

PostgreSQL unicode

I was having trouble storing unicode strings in my postgresql database.

DISCLAIMER: I'm still really struggling with Unicode in Python, but I think I got a little closer to understanding today!

When I installed and configured PostgreSQL for the first time, the default setting for character set was LATIN-1, which is annoying. I kept running into problems saving bookmarks in subMarks when the page titles had unicode characters in them (the emdash has a lot to answer for).

In order to fix this, I had to convert my database to UTF-8. You can't actually do this though, instead, you have to create a new database, and dump the data into it. Creating a new UTF-8 database is fairly straightforward:

$ createdb -E UTF8 -T template0 NEW_DB_NAME

I used some instructions I found for transferring the data, however, I didn't drop the cluster and create a new one - I have other databases running and I really couldn't be bothered with all the hassle, I just created a new database and left the old one where it was. Basically, I did this:

$ pg_dump --blobs --oids OLD_DB_NAME > OLD_DB_NAME.latin1.sql
$ pg_dump --blobs --oids --encoding=UTF-8 OLD_DB_NAME > OLD_DB_NAME.utf8.sql

then:

psql --set ON_ERROR_STOP=on NEW_DB_NAME < OLD_DB_NAME.utf8.sql

That all went fine, but the next step was causing me some serious problems: connecting to the database from my python web app.

Since I'm running PostgreSQL 8.4, I couldn't specify the client_encoding as part of the DSN, it needs a separate step:

conn = psycopg2.connect(app.config['DSN'])
conn.set_client_encoding('UTF8')

But that isn't quite enough. UTF-8 is not Unicode so Python was getting upset with the encoding of the strings it got from the database.

My Googlefu was strong at this point though, I discovered the missing piece in a bug filing for Django. Adding the line:

psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)

to the top of my database code did the job. You can view the code change in this commit.

This got me a little closer to finally getting Python Unicode!