Export text content to text file without \n mark

Question

When I try to export the text content of a field, and that content have carriage return characters, that chars are output like \N string.

For example:

create table foo ( txt text );
insert into foo ( txt ) values ( 'first line
second line
...
and other lines');
copy foo ( txt ) to '/tmp/foo.txt';

I want to return the following (a):

first line
second line
...
and other lines

But, output is (b):

first line\Nsecond line\N...\Nand other lines

Anybody knows how to get the (a) output?

Answer 1

The \N comes from the fact that one line must correspond to one database row.

This rule is relaxed for the CSV format where multi-line text is possible but then a quote character (by default: ") would enclose the text.

If you want multi-line output and no enclosing character around it, you shouldn't use COPY but SELECT.

Assuming a unix shell as the execution environment of the caller, you could do:

psql -A -t -d dbname -c 'select txt from foo' >/tmp/file.txt

Answer 2

Have you tried: \r\n?

Here's another solution that might work:

E'This is the first part \\n And this is the second'

via https://stackoverflow.com/a/938/1085891

Also, rather than copy the other responses, see here: String literals and escape characters in postgresql