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

User · Answer

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  gt  create table EscapeTest  text varchar 50    CREATE TABLE milen  gt  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  gt  select   from EscapeTest            text ------------------------  This will be inserted   This will not be  1 row   milen  gt

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[string] String literals and escape characters in postgresql

Examples related to string

Examples related to postgresql

Examples related to escaping