如何在 postgres 前端 COPY 中指定一个选项卡

我想使用 psql“ copy”命令将数据从以 tab 分隔的文件拉到 Postgres。我正在使用这个命令:

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

但是我得到了这样的警告(这个表实际上装载得很好) :

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'.

如果“ t”不正确，如何指定选项卡？

75585 次浏览

小开

最佳答案

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 ';'

小开

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

小开

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;