CODEWITHSUNDEEP
postgresqlpsql
Luís de Sousa
Luís de Sousa

Reputation: 6842

Exporting new line characters to text in Postgres

There is a text field in a Postgres database containing new lines. I would like to export the content of that field to a text file, preserving those new lines. However, the COPY TO command explictly transforms those characters into the \n string. For example:

$ psql -d postgres -c "COPY (SELECT CHR(10)) TO '/tmp/out.txt';"
COPY 1
$ cat /tmp/out.txt
\n

This behaviour seems to match the short description in the documents:

Presently, COPY TO will never emit an octal or hex-digits backslash sequence, but it does use the other sequences listed above for those control characters.

Is there any workaround to get the new line in the output? E.g. that a command like:

$ psql -d postgres -c "COPY (SELECT 'A line' || CHR(10) || 'Another line') TO '/tmp/out.txt';"

Results in something like:

A line
Another line

Update: I do not wish to obtain a CSV file. The output must not have headers, column separators or column decorators such as quotes (exactly as exemplified in the output above). The answers provided in a different question with COPY AS CSV do not fulfil this requirement.

Upvotes: 1

Views: 1105

Answers (1)

Adrian Klaver
Adrian Klaver

Reputation: 19665

Per my comment:

psql -d postgres -U postgres -c "COPY (SELECT CHR(10)) TO '/tmp/out.txt' WITH CSV;"
Null display is "NULL".
COPY 1

 cat /tmp/out.txt 
"
"


psql -d postgres -U postgres -c "COPY (SELECT 'A line' || CHR(10) || 'Another line') TO '/tmp/out.txt' WITH CSV;"
Null display is "NULL".
COPY 1

cat /tmp/out.txt 
"A line
Another line"

Using the CSV format will maintain the embedded line breaks in the output. This is explained here COPY under CSV Format

The values in each record are separated by the DELIMITER character. If the value contains the delimiter character, the QUOTE character, the NULL string, a carriage return, or line feed character, then the whole value is prefixed and suffixed by the QUOTE character, and any occurrence within the value of a QUOTE character or the ESCAPE character is preceded by the escape character. You can also use FORCE_QUOTE to force quotes when outputting non-NULL values in specific columns.

...

Note

CSV format will both recognize and produce CSV files with quoted values containing embedded carriage returns and line feeds. Thus the files are not strictly one line per table row like text-format files.

UPDATE

Alternate method that does not involve quoting, using psql.

create table line_wrap(id integer, fld_1 varchar);

insert into line_wrap values (1, 'line1
line2');

insert into line_wrap values (2, 'line3
line4');

select fld_1 from line_wrap
\g (format=unaligned tuples_only=on) out.txt

cat out.txt 
line1
line2
line3
line4

Upvotes: 2

Related Questions