140

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?

Damjan Pavlica
  • 31,277
  • 10
  • 71
  • 76
rjohnston
  • 7,153
  • 8
  • 30
  • 37

5 Answers5

157

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');
rjohnston
  • 7,153
  • 8
  • 30
  • 37
  • 5
    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 – Alexander Aug 14 '12 at 14:02
  • 2
    For PostgreSQL 9.2 see: http://www.postgresql.org/docs/9.2/interactive/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE – Pitt Nov 16 '12 at 12:37
  • `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. – Stew Dec 31 '15 at 17:09
53

Cool.

I also found the documentation regarding the E:

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

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 \xhexdigits, 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 ''.

Michael Stum
  • 177,530
  • 117
  • 400
  • 535
6

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.

eppesuig
  • 1,375
  • 2
  • 12
  • 28
  • 1
    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. –  Feb 09 '11 at 23:38
  • 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? – eppesuig Nov 17 '11 at 12:14
  • 1
    This is the right answer. Modern versions of PG now default to having it on. – jpmc26 Jan 03 '17 at 17:37
3

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=>
Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
  • Please try the test case I gave and you will see it for yourself. – rjohnston Sep 22 '08 at 04:50
  • Interesting, looks like the problem was in the JDBC driver then, because the text coming out of the database was very definitely being truncated... – rjohnston Jan 18 '09 at 23:59
  • 3
    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. – Bryson Mar 03 '14 at 20:18
0

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?

Community
  • 1
  • 1
  • 1
    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 – roberthuttinger Aug 31 '12 at 15:42