July 21st, 2010 by depesz | Tags: , , , , , | 7 comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

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.

  1. 7 comments

  2. Jul 21, 2010

    Perhaps because of some setting in your CMS, the single quote (ASCII) character (Unicode apostrophe, U0027), appears ok in the quoted texts (SQL code), but in the normal text is converted to a LEFT SINGLE QUOTATION MARK (U2018) (0x91 in Windows-1252 charset).

  3. Jul 21, 2010

    @Hernan:
    I guess it’s wordpress thing – not sure why it is so or how to turn it off.

  4. Jul 21, 2010

    Perhaps this?
    http://www.semiologic.com/software/unfancy-quote/

  5. Jul 22, 2010

    I probably could install it, but it’s too much hassle for single blogpost that would benefit from it. I’ll just change the important apostrophes into html entity, and it should be fine.

  6. # Thomas
    Aug 13, 2010

    I’m having troubles inserting data that contains backslashes via \copy like this :

    \copy table_a from data.csv delimiter as ‘|’ null as ”

    with data.csv containing stuff like

    test|more\|end|

    Because the pipe-delimiter is being escaped by the backslash after “more” the import fails.

    Currently I’m duplicating the backslashes with a perl-script, so it works, but, do you have a smarter idea ?

    BTW, I really enjoy your stuff … :-)

  7. # Thomas
    Aug 13, 2010

    Oops, I forgot to mention the real point, obviously ‘standard_conforming_strings’ doesn’t help me in this case, it’s activated when the above import fails.

  8. Aug 13, 2010

    @Thomas:

    change escape character in copy command.

    For example:

    COPY …. delimiter as ‘|’ null as ” escape as ‘~’

Leave a comment