\COPY to accept non UTF-8 chars in CHAR columns

Matthias Apitz <guru@unixarea.de> writes:

In short, it there a way to let \COPY accept such broken ISO bytes, just
complaining about, but not stopping the insert of the row?

No. We don't particularly believe in the utility of invalid data.

If you don't actually care about what encoding your data is in,
you could use SQL_ASCII as the database "encoding" and thereby
disable all UTF8-specific behavior. Otherwise, maybe this conversion
is a good time to clean up the mess?

regards, tom lane

On Sat, Mar 28, 2020 at 4:46 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Matthias Apitz <guru@unixarea.de> writes:

In short, it there a way to let \COPY accept such broken ISO bytes, just
complaining about, but not stopping the insert of the row?

No. We don't particularly believe in the utility of invalid data.

If you don't actually care about what encoding your data is in,
you could use SQL_ASCII as the database "encoding" and thereby
disable all UTF8-specific behavior. Otherwise, maybe this conversion
is a good time to clean up the mess?

Something like this approach might be useful for fixing the CSV file:

https://codereview.stackexchange.com/questions/185821/convert-a-mix-of-latin-1-and-utf-8-to-proper-utf-8

I haven't tested that program but it looks like the right sort of
approach; I remember writing similar logic to untangle the strange
mixtures of Latin 1, Windows 1252, and UTF-8 that late 90s browsers
used to send. That sort of approach can't fix every theoretical
problem (some valid Latin1 sequences are also valid UTF-8 sequences)
but it's doable with text in European languages.

"Thomas" == Thomas Munro <thomas.munro@gmail.com> writes:

Thomas> Something like this approach might be useful for fixing the CSV file:

Thomas> https://codereview.stackexchange.com/questions/185821/convert-a-mix-of-latin-1-and-utf-8-to-proper-utf-8

Or:

perl -MEncode -pe '
use bytes;
sub c { decode("UTF-8",shift,sub { decode("windows-1252", chr(shift)) }); }
s/([\x80-\xFF]+)/encode("UTF-8",c($1))/eg' <infile >outfile

--
Andrew (irc:RhodiumToad)

On 27/03/20, Andrew Gierth (andrew@tao11.riddles.org.uk) wrote:

"Thomas" == Thomas Munro <thomas.munro@gmail.com> writes:

Thomas> Something like this approach might be useful for fixing the CSV file:

Thomas> https://codereview.stackexchange.com/questions/185821/convert-a-mix-of-latin-1-and-utf-8-to-proper-utf-8

Or:

perl -MEncode -pe '
use bytes;
sub c { decode("UTF-8",shift,sub { decode("windows-1252", chr(shift)) }); }
s/([\x80-\xFF]+)/encode("UTF-8",c($1))/eg' <infile >outfile

Or:

iconv -f WINDOWS-1252 -t UTF-8 -c < tempfile2 > tempfile3

"Rory" == Rory Campbell-Lange <rory@campbell-lange.net> writes:

Rory> Or:

Rory> iconv -f WINDOWS-1252 -t UTF-8 -c < tempfile2 > tempfile3

No. That's just a conversion of win1252 to utf8 without regard for any
UTF8 that might already be present in the input. Any such input will end
up double-encoded, requiring further work to fix.

--
Andrew (irc:RhodiumToad)

On 27/03/20, Andrew Gierth (andrew@tao11.riddles.org.uk) wrote:

"Rory" == Rory Campbell-Lange <rory@campbell-lange.net> writes:

Rory> Or:

Rory> iconv -f WINDOWS-1252 -t UTF-8 -c < tempfile2 > tempfile3

No. That's just a conversion of win1252 to utf8 without regard for any
UTF8 that might already be present in the input. Any such input will end
up double-encoded, requiring further work to fix.

My apologies. I missed the subtlety of the substitution in your perl code.

El día sábado, marzo 28, 2020 a las 09:40:30a. m. +1300, Thomas Munro escribió:

On Sat, Mar 28, 2020 at 4:46 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Matthias Apitz <guru@unixarea.de> writes:

In short, it there a way to let \COPY accept such broken ISO bytes, just
complaining about, but not stopping the insert of the row?

No. We don't particularly believe in the utility of invalid data.

If you don't actually care about what encoding your data is in,
you could use SQL_ASCII as the database "encoding" and thereby
disable all UTF8-specific behavior. Otherwise, maybe this conversion
is a good time to clean up the mess?

Something like this approach might be useful for fixing the CSV file:

https://codereview.stackexchange.com/questions/185821/convert-a-mix-of-latin-1-and-utf-8-to-proper-utf-8

I haven't tested that program but it looks like the right sort of
approach; I remember writing similar logic to untangle the strange
mixtures of Latin 1, Windows 1252, and UTF-8 that late 90s browsers
used to send. That sort of approach can't fix every theoretical
problem (some valid Latin1 sequences are also valid UTF-8 sequences)
but it's doable with text in European languages.

Thomas,

This is a very good finding, thanks for this.

I played around a bit with the original code, and tested some
modifications to fit better in our problem... It works fine:

- it translates any ISO char, for example 0xfc (German Umlaut ü in ISO), into the
correct UTF-8 coding 0xc3bc:

perl -e 'print pack("H*", "4040fc4040")' | ./convert2properUTF8 | hexdump -C
00000000 40 40 c3 bc 40 40 |@@..@@|
00000006

- it translates a situation where 0xc3bc (German Umlaut ü in UTF-8
coding) was broken into two columns, one terminating in 0xc3 and the 2nd
row starting with 0xbc; this would give:

perl -e 'print pack("H*", "c3")' | ./convert2properUTF8 | hexdump -C
00000000 c3 83 |..|
00000002
perl -e 'print pack("H*", "bc40")' | ./convert2properUTF8 | hexdump -C
00000000 c2 bc 40 |..@|
00000003

i.e. 0xc3 is translated to 0xc383 and the 2nd half, the 0xbc to
0xc2bc, both translations have nothing to do with the original split 0xc3bc, and
perhaps in this case it would be better to spill out a blank 0x40 for
each of the bytes which formed the 0xc3bc.

But this we will discuss here and align the code to our use cases.

Thanks again

matthias

--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

"Matthias" == Matthias Apitz <guru@unixarea.de> writes:

Matthias> i.e. 0xc3 is translated to 0xc383 and the 2nd half, the
Matthias> 0xbc to 0xc2bc, both translations have nothing to do with
Matthias> the original split 0xc3bc, and perhaps in this case it
Matthias> would be better to spill out a blank 0x40 for each of the
Matthias> bytes which formed the 0xc3bc.

If the only malformed sequences are there as a result of splitting up
valid sequences, then you could do something like convert all invalid
sequences to (sequences of) noncharacters, then once the data is
imported, fix it up by adjusting how the data is split and regenerating
the correct sequence (assuming your application allows this).

For example you could encode an arbitrary byte xy as a sequence of two
codepoints U+FDDx U+FDEy (the range FDD0-FDEF are all defined as
noncharacters).

--
Andrew (irc:RhodiumToad)