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@xxxxxxxxxxxxx> wrote: > > Matthias Apitz <guru@xxxxxxxxxxx> 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@xxxxxxxxxxx, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
