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

Laurenz Albe · Accepted Answer · 2020-07-03 19:01:52Z

1

Just figure out the encoding of the file, then go

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

answered Jul 3, 2020 at 19:01

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Willem Over a year ago

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

Collectives™ on Stack Overflow

Postgresql: copy from gzip + invalid byte sequence encoding

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related