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?
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
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
