Postgres COPY file with encoding LATIN1 into table with encoding UTF

Question

I have a problem during bulk insert. I'm trying do bulk insert from file with encoding LATIN1 into table where database with encoding UTF8.

invalid byte sequence for encoding "UTF8": 0xc33f 

When I do SET CLIENT_ENCODING='LATIN1' and after do COPY from console, it works OK. but JDBC say me that he can't do SET CLIENT_ENCODING.

Please, could you suggest solution how I can workaround problem. Thanks!

Answer 1

Try to set allowEncodingChanges connection parameter to true to allow (temporarily) changing client encoding to LATIN1. According to Table 22-2 PostgreSQL should handle automatic character set conversion between LATIN1 (client) and UTF8 (server).

The client_encoding setting is set by the driver and should not be altered. If the driver detects a change it will abort the connection. There is one legitimate exception to this behavior though, using the COPY command on a file residing on the server's filesystem. The only means of specifying the encoding of this file is by altering the client_encoding setting.

Enable this only if you need to override the client encoding when doing a copy.