CODEWITHSUNDEEP
postgresql
sayali
sayali

Reputation: 151

In Postgres, how to preserve newline character in COPY TO command?

One of the column of my table contains string with '\n'.

postgres=# select * from stringtest ;

 id |    inputstr

----+-----------------

  1 | Name : NOC\r   +

    | \r             +

    | Detail : Detail

(1 row)

But when I retrieve same table with COPY TO, new line character is getting escaped

postgres=# COPY (select * from stringtest) TO STDOUT WITH DELIMITER E'\t' ;

1       Name : NOC\r\n\r\nDetail : Detail

How can I make COPY TO command to replace '\n' with new line ??

Expected output :

postgres=# COPY (select * from stringtest) TO STDOUT WITH DELIMITER E'\t' ;

1       Name : NOC\r
        \r
        Detail : Detail

How to achieve this ??

Upvotes: 9

Views: 13853

Answers (3)

Fatima Santos
Fatima Santos

Reputation: 11

I use this way:

(SELECT nextval('seq'), E''||inputstr||'' FROM strongest);

I think it is possible using COPY,like this:

postgres=# COPY (select id, E''||storage_dex||'', * from stringtest);

Upvotes: 1

Shogan Aversa-Druesne
Shogan Aversa-Druesne

Reputation: 339

You need to evaluate the escapes. Something like this will work 

echo -e "`psql -c "COPY stringtest TO STDOUT WITH DELIMITER E'\t' ;"`"

This is properly quoted so you wont have spaces crushed. NB: it will evaluate all escapes not just '\n'


For the reverse operations COPY ... FROM stdin ... Just escape by adding a backslash before the newline. The same can be done for the tab or whatever delimiter you choose.

If you are typing in the terminal directly or running a file \

postgres=# COPY stringtest FROM stdin WITH DELIMITER E'\t' ;
>>Name : NOC\r\
>>\r\
>>Detail : Detail
>>\.
postgres=#

Or working with data in a scripting language here is the python equivalents

 forward or reverse 
 .replace('\\n','\n') or .replace('\n','\\n')
 .replace('\\t','\t') or .replace('\t','\\t')

Upvotes: 2

Legato
Legato

Reputation: 1081

There are 2 text formats you can use:

  1. Simple delimited text
  2. CSV

I believe that with the first format (the one you used) each logical line is also a physical line, meaning that there's no way around a literal '\n'. However if you use the CSV option, you can quote the string with an embedded newline:

test=# copy (select * from stringtest) to stdout csv;
1,"line one
linetwo"

Not 100% your expected output, but if CSV is fine for you - this will do the trick.

With tab delimiter:

test=# copy (select * from stringtest) to stdout csv delimiter E'\t';
1   "line one
linetwo"

Upvotes: 7

Related Questions