String literals and escape characters in postgresql

Question

Attempting to insert an escape character into a table results in a warning.

For example:

create table EscapeTest (text varchar(50));

insert into EscapeTest (text) values ('This is the first part \n And this is the second');

Produces the warning:

WARNING:  nonstandard use of escape in a string literal

(Using PSQL 8.2)

Anyone know how to get around this?

Kasandrakasevich

Answer 1

Partially. The text is inserted, but the warning is still generated.

I found a discussion that indicated the text needed to be preceded with 'E', as such:

insert into EscapeTest (text) values (E'This is the first part \n And this is the second');

This suppressed the warning, but the text was still not being returned correctly. When I added the additional slash as Michael suggested, it worked.

As such:

insert into EscapeTest (text) values (E'This is the first part \\n And this is the second');

Kasandrakasevich

Note that on PostgreSQL 9.0 E'testing\\x20double-slash' will evaluate as 'testing\\x20double-slash', so only single-slash approach works for E'string' style literals – Chandler

For PostgreSQL 9.2 see: postgresql.org/docs/9.2/interactive/… – Tompkins

psql \copy note: I found that E'\n' was written to file as '\n' rather than as a newline when I used it in the query argument to psql's `\copy' meta-command. – Tsarevna

Answer 2

Cool.

I also found the documentation regarding the E:

http://www.postgresql.org/docs/8.3/interactive/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS

_{Quote formatting by 'Randall'}

PostgreSQL also accepts "escape" string constants, which are an extension to the SQL standard. An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g. E'foo'. (When continuing an escape string constant across lines, write E only before the first opening quote.) Within an escape string, a backslash character (\) begins a C-like backslash escape sequence, in which the combination of backslash and following character(s) represents a special byte value:
\b is a backspace,
\f is a form feed,
\n is a newline,
\r is a carriage return,
\t is a tab.

Also supported are
\<digits>, where <digits> represents an octal byte value, and
\x<hexdigits>, where <hexdigits> represents a hexadecimal byte value.
(It is your responsibility that the byte sequences you create are valid characters in the server character set encoding.)

Any other character following a backslash is taken literally. Thus, to include a backslash character, write two backslashes (\\). Also, a single quote can be included in an escape string by writing \', in addition to the normal way of ''.

Numeral

Answer 3

The warning is issued since you are using backslashes in your strings. If you want to avoid the message, type this command "set standard_conforming_strings=on;". Then use "E" before your string including backslashes that you want postgresql to intrepret.

Fasces

Not really. If I have standard_conforming_strings=on and run the command \copy xxxxxxxxxxx FROM /support01/db/data/xxxxxxxxx_7F.txt DELIMITER AS E'\x7f', I get parse error at "'\x7f'". If I have the standard_conforming_strings=off; and use the same command above without the E and the Quotes... ( DELIMITER AS \x7f) I get the warning message, but the data loads fine. So your statement may be correct but not in this case. – Stheno

I was referring to strings in SQL statements, while you are now using a psql command. Do you get the same error using the COPY command instead of \copy? – Fasces

This is the right answer. Modern versions of PG now default to having it on. – Aldridge

Answer 4

I find it highly unlikely for Postgres to truncate your data on input - it either rejects it or stores it as is.

milen@dev:~$ psql
Welcome to psql 8.2.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

milen=> create table EscapeTest (text varchar(50));
CREATE TABLE
milen=> insert into EscapeTest (text) values ('This will be inserted \n This will not be');
WARNING:  nonstandard use of escape in a string literal
LINE 1: insert into EscapeTest (text) values ('This will be inserted...
                                              ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
INSERT 0 1
milen=> select * from EscapeTest;
          text
------------------------
 This will be inserted
  This will not be
(1 row)

milen=>

Longbow

Please try the test case I gave and you will see it for yourself. – Kasandrakasevich

Interesting, looks like the problem was in the JDBC driver then, because the text coming out of the database was very definitely being truncated... – Kasandrakasevich

Postgres does truncate data on input in some very specific situations. For example, a character varying(4) column given the input "test " (two spaces following the word, 6 characters) will truncate the spaces and store the value "test". As a general rule, however, you can assume that Postgres will error rather than truncate your data. – Jaquith

Answer 5

Really stupid question: Are you sure the string is being truncated, and not just broken at the linebreak you specify (and possibly not showing in your interface)? Ie, do you expect the field to show as

This will be inserted \n This will not be

or

This will be inserted

This will not be

Also, what interface are you using? Is it possible that something along the way is eating your backslashes?

Serles

this happened to me. The text was being inserted into a text box, viewed the source, and sure enough, there was a quote and the entire text was present, just not visible – Sox