108

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?

Timur Shtatland
  • 12,024
  • 2
  • 30
  • 47
Chris Curvey
  • 9,738
  • 10
  • 48
  • 70
  • 14
    Try what the error message suggests: `\copy cm_state from 'state.data' with delimiter E'\t' null as ';'` – Frank Farmer May 24 '11 at 15:48
  • 2
    E begins an escape sequence. Think of E much like you would a double quoted string in C. E'\t' == "\t". – Sean May 24 '11 at 19:59

3 Answers3

223

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 ';'
Meeh
  • 2,538
  • 3
  • 18
  • 20
Seth Robertson
  • 30,608
  • 7
  • 64
  • 57
  • 19
    Thanks! This also solved the less informative error `COPY delimiter must be a single one-byte character` for me. Adding it here so search engines can pick up on it. – Dawn Drescher Nov 26 '15 at 10:15
  • This should be another answer with error message. Just so that the search engines pick it up :) – RK Kuppala Apr 07 '16 at 08:25
7

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

user4372693
  • 71
  • 1
  • 1
  • 2
    the default delimiter for text file is a tab.. I have used this and it works .. https://www.postgresql.org/docs/9.2/static/sql-copy.html – user4372693 Mar 08 '17 at 20:16
  • 1
    This helps when you want to put the command in a shell script -- `$$\t$$` doesn't seem to work, oddly. – Otheus Aug 23 '17 at 18:31
  • This works for data generated by `pg_dump` without other options specified. – GuiRitter Apr 04 '19 at 11:04
  • use `\COPY tabname(col1, col2) FROM 'in.csv' with (FORMAT text, HEADER)` if csv column split by \t & have header line //header line just skip, columns order from sql. – yurenchen Apr 02 '22 at 20:01
1

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;
B. Balázs Musulin
  • 21
  • 2