Postgresql: copy from gzip + invalid byte sequence encoding

Question

In this post it's outlined how to copy the contents from a csv in a .gzip file into a postgresql table: Importing zipped CSV file into PostgreSQL.

copy tp from program 'zcat /tmp/tp.csv.gz';

My question is whether it's possible to also convert the encoding of the csv file within the gzip, as shown here invalid byte sequence for encoding "UTF8"

so something like this:

copy tp from program 'iconv -f original_charset -t utf-8 zcat /tmp/tp.csv.gz > stdin';

I get the following error when applying copy from with zcat:

psycopg2.errors.CharacterNotInRepertoire: invalid byte sequence for encoding "UTF8": 0xba CONTEXT: COPY tp, line 677117

When unpacked the csv file is encoded as ASCII

file tp.csv

score 1 · Accepted Answer · answered Jul 03 '20 at 19:01

1

Just figure out the encoding of the file, then go

COPY tp FROM PROGRAM 'zcat /tmp/tp.csv.gz' (ENCODING 'whatever');

answered Jul 03 '20 at 19:01

Laurenz Albe

thank you Laurenz, the following worked for me with ASCII encoding (which I read should be a subset of UTF8, nonetheless it gave an error): COPY tp FROM PROGRAM 'zcat /tmp/tp.csv.gz' (ENCODING 'latin9'); – Willem Jul 03 '20 at 19:39

1 Answers1