PostgreSQL newline character

Question

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 1

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.

---

update: a good answer from @thedayturns, where you can have a simpler query:

E'test line 1\ntest line 2'