PostgreSQL newline character

string postgresql newline
Alimin picture Alimin · Mar 16, 2016 · Viewed 65.1k times · Source

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

But unfortunately I just get this result from my script when I run it in sql editor:

test line 1 test line 2

Answer

a_horse_with_no_name picture a_horse_with_no_name · Mar 16, 2016

The backslash has no special meaning in SQL, so '\n' is a backslash followed by the character n

To use "escape sequences" in a string literal you need to use an "extended" constant:

select 'test line 1'||E'\n'||'test line 2';

Another option is to use the chr() function:

select 'test line 1'||chr(10)||'test line 2';

Or simply put the newline in the string constant:

select 'test line 1
test line 2';

Whether or not this is actually displayed as two lines in your SQL client, depends on your SQL client.

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
String literals and escape characters in postgresql

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: …

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