SELECT Surprise

While experimenting with unicode values in Python, PostgreSQL and pyscopg2, I ran a little query like this:

>>> cur.execute("SELECT (note_id, body) FROM troy.morenotes")
>>> cur.fetchall()
[('(1,"The rain in Spain falls mainly in the plains.")',),
('(2,"Twenty dwarves did handstands on the carpet.")',),
('(5,I\xc3\xb1t\xc3\xabrn\xc3\xa2ti\xc3\xb4n\xc3\xa0liz\xc3\xa6ti\xc3\xb8n)',)]


At first I thought the results were string-mashed by psycopg, as it does something similar when given user defined types. After poking around the encoding example, I tried this:

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


Still no luck. So I recreated the database with an explicit encoding:

$ createdb -E UNICODE -O dba -h localhost -U dba apps


Again, no change. Then I realized that the query was working as expected in the application, just not in my ad-hoc query. Ding-ding! The query shouldn’t have had the parenthesis around the column list. Restated, it works correctly:

>>> cur.execute("SELECT note_id, body FROM troy.morenotes")
>>> cur.fetchall()
[(1, u'The rain in Spain falls mainly in the plains.'),
(2, u'Twenty dwarves did handstands on the carpet.'),
(5, u'I\xf1t\xebrn\xe2ti\xf4n\xe0liz\xe6ti\xf8n')]


To more fully understand what was happening, I re-read the “SELECT List” section in the PG SELECT documentation. No reference there, but I managed to find one in Section 4.2.11. Row Constructors. And finally, I confirmed my sanity by comparing the ROW constructor output to the original:

>>> cur.execute("SELECT ROW(note_id, body) FROM troy.morenotes")
>>> rows = cur.fetchall()
>>> cur.execute("SELECT (note_id, body) FROM troy.morenotes")
>>> results = cur.fetchall()
>>> rows == results
True


So why blog about this? It seems to be one of those little trivial mistakes that is best not shared. (Too Much Information, yeah?). But in my case, I’m using this as a public reminder to not over-parenthesize, which is something I do (until now) reflexively.

Advertisement