Generally I write about new features, but this change is relatively important.

Yesterday, on 20th of July, Robert Haas committed following change:

Log Message:
-----------
Change the default value of standard_conforming_strings to on.
 
This change should be publicized to driver maintainers at once and
release-noted as an incompatibility with previous releases.

What is this, and why is it important?

Let's assume you want to select some value which contains ' character. Since string delimiters are also ', we need some way to escape it.

For long time, you could:

$ SELECT 'guns \'n roses';
   ?COLUMN?    
---------------
 guns 'n roses
(1 row)

Which is all fine, and looks OK to anyone programming in other languages, but there is one slight problem. SQL standard doesn't like it.

So. Some time ago (not sure when exactly, and it doesn't really matter, PostgreSQL introduced escape string constants, which look like these:

$ SELECT E'guns \'n roses';
   ?COLUMN?    
---------------
 guns 'n roses
(1 row)

and, when you used plain strings with backslash-escaping – it produced warning:

$ SELECT 'guns \'n roses';
WARNING:  nonstandard USE OF \' in a string literal
LINE 1: select 'guns \'n roses';
               ^
HINT:  USE '' TO WRITE quotes IN strings, OR USE the escape string syntax (E'...').
   ?COLUMN?    
---------------
 guns 'n roses
(1 row)

It did work just the way it worked earlier, but with warning.

Warning, of course could have been disabled ( escape_string_warning GUC ), and you could also disallow backslash escaped strings in ', by setting ‘standard_conforming_strings' GUC to ‘on'.

Now, with 9.1, default value of standard_conforming_strings will be on. Which means that our test select will yield:

$ SELECT 'guns \'n roses';
>>

That is – PostgreSQL treats now \ as literal character, and since number of ' characters is odd – it means that one of string constants is not finished, and the query is not ready yet.

Simpler example:

$ SELECT 'first line\nsecond line';
  ?COLUMN?
-------------
 FIRST line +
 SECOND line
(1 ROW)
 
$ SET standard_conforming_strings = ON;
SET
 
$ SELECT 'first line\nsecond line';
        ?COLUMN?
-------------------------
 FIRST line\nsecond line
(1 ROW)

So. How to make query with ' or new line character? Shortest answer is: properly. Example:

$ SELECT 'guns ''n roses';
   ?COLUMN?
---------------
 guns 'n roses
(1 row)
 
$ select E'FIRST line\nsecond line';
  ?column?
-------------
 first line +
 second line
(1 row)

When you're writing your own queries fully, it's usually not a big problem, but when you're using something that builds them for you (ORM, or even some interface that inlines parameters) – it will have to be fixed (if it wasn't fixed earlier).

Of course you can change the value of standard_conforming_strings back to ‘off' – but that is not really a solution.