Re: COPY command use UTF-8 encoding and NOT

Am Mittwoch, den 06.04.2005, 18:12 -0400 schrieb David Gagnon:
> Hi all,
>
>   I ran into this problem and want to share and have a confirmation.
>
> I tried to use COPY function to load bulk data.  I craft myself a
> UNICODE file from a MSSQL db.  I can't load it into the postgresql.  I
> always get the error: CONTEXT:  COPY vd, line 1, column vdnum: "ÿþ1"
>
> The problem is that both file are exactly the same... I found that
> pg_dump create in fact a UTF-8 (Confirm please) file with is UNICODE
> but with variable length encoding (Ie: Some character user 8 bytes and
> other 16 bytes ...).  See for detail:
> http://www.cl.cam.ac.uk/~mgk25/unicode.html#utf-8.  The file I crafted
> is a true UNICODE (16 bytes or UCS-2) file (Confirm please)
>
> So here is the content of the file:
> UTF-8 (Postgresql dump):
> 1    1    1    AC    COLUMNÿACNUMÿACDESCÿACDELPAIÿ
>
> UNICODE (crafted from mssql)
> 1    1    1    AC    COLUMNÿACNUMÿACDESCÿACDELPAIÿ
>
> HEX representation UTF-8 (Postgresql dump):
>
> 00000000:31 09 31 09 31 09 41 43 09 43 4f 4c 55 4d 4e c3
> 1.1.1.AC.COLUMNÃ
> 00000010:bf 41 43 4e 55 4d c3 bf 41 43 44 45 53 43 c3 bf
> ¿ACNUMÿACDESCÿ
> 00000020:41 43 44 45 4c 50 41 49 c3 bf
> ACDELPAIÿ
>
> HEX representation UNICODE (crafted from mssql)
> 00000000:ff fe 31 00 09 00 31 00 09 00 31 00 09 00 41 00
> ÿþ1...1...1...A.
> 00000010:43 00 09 00 43 00 4f 00 4c 00 55 00 4d 00 4e 00
> C...C.O.L.U.M.N.
> 00000020:ff 00 41 00 43 00 4e 00 55 00 4d 00 ff 00 41 00
> ÿ.A.C.N.U.M.ÿ.A.
> 00000030:43 00 44 00 45 00 53 00 43 00 ff 00 41 00 43 00
> C.D.E.S.C.ÿ.A.C.
> 00000040:44 00 45 00 4c 00 50 00 41 00 49 00 ff 00
> D.E.L.P.A.I.ÿ.
>
> So postgresql bug with the FF FE that start the UNICODE document.  Is
> that normal UNICODE file starts with this FF FE ?! Note that I tried
> to delete those character but they aren`t visible...
>
> So am I right ? Is Postgresql using UTF-8 and don`t really understand
> UNICODE file (UCS-2)?  Is there a way I can make the COPY command with
> a UNICODE UCS-2 encoding

Yes, postgres Unicode means utf-8.
Windows programs which store unicode text are usually prepending
the file with a BOM (byte order mark) (just google for unicode
and BOM)
So you would need something to convert. For example I know python
can read BOM so it can be a matter of open it, read, encode it
into utf-8 and write it out again.

Regards
Tino Wildenhain