CODEWITHSUNDEEP
stringpostgresqlnewline
Alimin
Alimin

Reputation: 2747

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

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

test line 1 test line 2

Upvotes: 145

Views: 165515

Answers (3)

user7868
user7868

Reputation: 306

In Postgres, you can use dollar-quoted strings. Everything included pairs of dollar signs is treated as a string literal. You can just say

select $$test line 1
test line 2$$

Upvotes: 1

Andriod
Andriod

Reputation: 1339

If you want to search if a jsonb contains \n this works

SELECT * FROM table
WHERE column ~ '^.*\\n.*$'

Upvotes: 1

user330315
user330315

Reputation:

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:

select E'test line 1\ntest line 2';

Upvotes: 260

Related Questions