Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.


#108 Quoting doesn't work with PostgreSQL 9

Andriy Pylypenko


I'm using the pyPgSQL for quite a while, but recently I faced a trouble after migration to PostgreSQL 9. I found out that newly stored strings in the database are corrupted. After investigation I've created a patch which fixes the issue.

Let me explain the problem. The pyPgSQL library uses backslash escaped strings when using parameter binding. Lets use as an example the string: 'Here is a newline: \n'.

The PostgreSQL server has a special notation for backslash escaped strings, so the above string should be written with the E prefix: E'Here is a newline: \n'. If the E prefix is omitted then the PostgreSQL 9 sees it as 'Here is a newline: \\n' string, so the string actually two caracters - backslash and 'n' instead of a single newline character.

Before the version 9 the PostgreSQL just warned about use of an incorrect notation and processed the string as a backslash escaped string anyway. The PostgreSQL 9 has changed this behavior and the E prefix is now mandatory.

Taking into account that in pyPgSQL all the strings are forcibly casted to backslash escaped format during parameter binding, it is safe to always use the E prefix. This fixes the bug when working with PostgreSQL 9. It is also safe for earlier versions of the PostgreSQL, as this notation is recognized by them.

Please see the attached patch.