CODEWITHSUNDEEP
postgresqlcsv
Chris Curvey
Chris Curvey

Reputation: 10389

How to specify a tab in a postgres front-end COPY

I would like to use the psql "\copy" command to pull data from a tab-delimited file into Postgres. I'm using this command:

\copy cm_state from 'state.data' with delimiter '\t' null as ;

But I'm getting this warning (the table actually loads fine):

WARNING:  nonstandard use of escape in a string literal
LINE 1: COPY cm_state FROM STDIN DELIMITER '\t' NULL AS ';'
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.

How do I specify a tab if '\t' is not correct?

Upvotes: 109

Views: 88136

Answers (3)

B. Balázs Musulin
B. Balázs Musulin

Reputation: 61

Thx this E'\t' formatting. Escaping character works when importing tab separated (TSV) data files, and headers not only in CSV.

This way I have successful imported a TSV, but only with DELIMITER option as follows in this psql (contained excluded header)

\COPY mytable FROM 'mydata.tsv' DELIMITER E'\t' CSV HEADER;

Upvotes: 3

Seth Robertson
Seth Robertson

Reputation: 31451

Use E'\t' to tell postgresql there may be escaped characters in there:

\copy cm_state from 'state.data' with delimiter E'\t' null as ';'

Upvotes: 230

user4372693
user4372693

Reputation: 81

you can do this copy cm_state from stdin with (format 'text')

Upvotes: 8

Related Questions