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

PostgresqlCsv

Postgresql Problem Overview

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?

Postgresql Solutions

Solution 1 - Postgresql

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

Solution 2 - Postgresql

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

Solution 3 - Postgresql

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;
Categories
Recommended Postgresql Solutions
Recommended Csv Solutions

Previous Solution:

ORA-00904: invalid identifier

SqlDatabaseOracleOra 00904

Next Solution:

Get current date in milliseconds

Objective CCocoa TouchIosDateTime

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionChris CurveyView Question on Stackoverflow
Solution 1 - PostgresqlSeth RobertsonView Answer on Stackoverflow
Solution 2 - Postgresqluser4372693View Answer on Stackoverflow
Solution 3 - PostgresqlB. Balázs MusulinView Answer on Stackoverflow