CODEWITHSUNDEEP
postgresql
syam houng
syam houng

Reputation: 297

How to insert new line in PostgreSQL

I want to make new line when the column length > 100 in PostgreSQL query, here is my code:

SELECT message,  CASE WHEN length(message) > 100 
THEN  message||'\n' end as res FROM msg.list_trx

but my query doesn't work.

Upvotes: 5

Views: 9944

Answers (2)

Radek Postołowicz
Radek Postołowicz

Reputation: 4774

You are missing e right before new line literal (it is required for c-like special chars):

SELECT message,  CASE WHEN length(message) > 100 
THEN  message||e'\n' end as res FROM msg.list_trx

Have a look at http://www.postgresql.org/docs/9.0/static/sql-syntax-lexical.html at paragraph

4.1.2.2. String Constants with C-Style Escapes

for more info.

Upvotes: 10

klin
klin

Reputation: 121534

You can use regexp_replace(). The query below splits long messages into lines of 30 characters:

create table messages (id int, message text);
insert into messages values 
(1, 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt'),
(2, 'Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusantium doloremque');

select id, regexp_replace(message, '(.{30})', e'\\1\n', 'g')
from messages

 id |         regexp_replace         
----+--------------------------------
  1 | Lorem ipsum dolor sit amet, co+
    | nsectetur adipiscing elit, sed+
    |  do eiusmod tempor incididunt
  2 | Sed ut perspiciatis unde omnis+
    |  iste natus error sit voluptat+
    | em accusantium doloremque
(2 rows)

Upvotes: 1

Related Questions