String literals and escape characters in postgresql

string postgresql escaping
rjohnston picture rjohnston · Aug 4, 2008 · Viewed 269.2k times · Source

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?

Answer

rjohnston picture rjohnston · Aug 4, 2008

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');

Related questions

Difference between text and varchar (character varying)

What's the difference between the text data type and the character varying (varchar) data types? According to the documentation If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension. …

Read More
PostgreSQL newline character

How to use newline character in PostgreSQL? This is an incorrect script from my experiment: select 'test line 1'||'\n'||'test line 2'; I want the sql editor display this result from my script above: test line 1 test line 2 …

Read More
Changing a column type to longer strings in rails

At the first migration, I declared on a column content to be string Activerecord made it to be string(255) according to annotate gem. After I push the app to heroku, which uses postgres, if I enter in the form in …

Read More