Exporting new line characters to text in Postgres

Question

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.

I think what you are looking for is WITH CSV: " 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." — Adrian Klaver
– Adrian Klaver, Commented Jun 8, 2022 at 15:10

Adrian Klaver · Accepted Answer · 2022-06-09 15:35:58Z

2

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

edited Jun 9, 2022 at 15:35

answered Jun 8, 2022 at 15:16

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Luís de Sousa Over a year ago

I need to export the contents of this field to a text file, not a CSV. No headers, field separators, etc.

Adrian Klaver Over a year ago

Then COPY table_name(field_name) TO '/tmp/out.txt' WITH CSV. Unless you specify HEADER there will be no header and with a single field no delimiter. COPY is row(records) based statement so that is as far as you can take it.

Adrian Klaver Over a year ago

See my UPDATE for an alternate method.

Luís de Sousa Over a year ago

psql can easily be automated, this is a valuable method. You can remove the section with COPY as it definitely does not produce the kind of files I need. Also the bit with Null display is "NULL". is returning an error: "Command 'Null' not found" (postgres-client 14).

Adrian Klaver Over a year ago

The Null display is "NULL" COPY 1 portions are from connecting to the database and the return value of the command. It just means I have \pset null 'NULL' set in my .psqlrc file.

Collectives™ on Stack Overflow

Exporting new line characters to text in Postgres

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related