Thread: BUG #5944: COPY FROM doesn't work with international characters
The following bug has been logged online: Bug reference: 5944 Logged by: Nathan Davalos Email address: n.davalos@sharedmarketing.com PostgreSQL version: 9 Operating system: Windows XP/2003 Description: COPY FROM doesn't work with international characters Details: I'm trying to load a tab delimited text file with COPY FROM under version 9. create table tmpintermediate ( AcNumber character varying(20), AcName character varying(50)); SET CLIENT_ENCODING TO 'WIN1251'; copy tmpintermediate from 'thefile.txt'; Sample contents of thefile: 230002 Alto Desempeño, S.A. De C.V. When using WIN1251 or WIN1252 I get nothing in the second field, it just ignores the data. Same thing for LATIN-1. When using UTF8 for client encoding I get this message: ERROR: invalid byte sequence for encoding "UTF8": 0xf16f2c20 CONTEXT: COPY tmpintermediate , line 1 Not sure what the problem is, I assumed encoding the database as UTF8 would just take any character. Inserting the data with an insert statement works perfectly normal. Is it something I'm doing wrong with COPY FROM? The database is UTF8, in 8.3 we were using WIN1252 for the database encoding, but when we migrated to 8.4 everything was forced to UTF8 regardless of what the locale was specified during install using pg_dumpall to migrate the data, so I just left things as UTF8. Not sure how to proceed with this one. We create all our databases using a customized template datatabase (we leave the ones that install with postgresql intact and untouched). The copy commands are being issued by pgadmin3 if that makes any difference. That and we use MicroOlap's PosgresDAC components for Delphi.
On 03/23/11 4:32 PM, Nathan Davalos wrote: > ... > SET CLIENT_ENCODING TO 'WIN1251'; > copy tmpintermediate from 'thefile.txt'; > > > Sample contents of thefile: > 230002 Alto Desempe=C3=B1o, S.A. De C.V. > > When using WIN1251 or WIN1252 I get nothing in the second field, it just > ignores the data. Same thing for LATIN-1. > > When using UTF8 for client encoding I get this message: > ERROR: invalid byte sequence for encoding "UTF8": 0xf16f2c20 > CONTEXT: COPY tmpintermediate , line 1 what is the byte (binary) encoding of the file? in hex, =C3=B1 in win1251 =3D=3D (no such character. win1251 is cyrillic) =C3=B1 in win1252 =3D=3D F1 =C3=B1 in UTF-8 =3D=3D C3 B1 =20=20
MzIzMzMwMzAzMDMyMDk0MTZDNzQ2RjIwNDQ2NTczNjU2RDcwNjVGMTZGMkMy MDUzMkU0MTJFMjA0NDY1MjA0MzJFNTYyRTBEMEENClRoZSBjaGFyYWN0ZXIg aW4gcXVlc3Rpb24gaXMgRjENCg0KLS0tLS1PcmlnaW5hbCBNZXNzYWdlLS0t LS0NCkZyb206IEpvaG4gUiBQaWVyY2UgW21haWx0bzpwaWVyY2VAaG9ncmFu Y2guY29tXSANClNlbnQ6IFdlZG5lc2RheSwgTWFyY2ggMjMsIDIwMTEgNjo0 OSBQTQ0KVG86IE5hdGhhbiBNLiBEYXZhbG9zDQpDYzogcGdzcWwtYnVnc0Bw b3N0Z3Jlc3FsLm9yZw0KU3ViamVjdDogUmU6IFtCVUdTXSBCVUcgIzU5NDQ6 IENPUFkgRlJPTSBkb2Vzbid0IHdvcmsgd2l0aCBpbnRlcm5hdGlvbmFsIGNo YXJhY3RlcnMNCg0KT24gMDMvMjMvMTEgNDozMiBQTSwgTmF0aGFuIERhdmFs b3Mgd3JvdGU6DQo+IC4uLg0KPiBTRVQgQ0xJRU5UX0VOQ09ESU5HIFRPICdX SU4xMjUxJzsNCj4gY29weSB0bXBpbnRlcm1lZGlhdGUgZnJvbSAndGhlZmls ZS50eHQnOw0KPg0KPg0KPiBTYW1wbGUgY29udGVudHMgb2YgdGhlZmlsZToN Cj4gMjMwMDAyCUFsdG8gRGVzZW1wZcOxbywgUy5BLiBEZSBDLlYuDQo+DQo+ IFdoZW4gdXNpbmcgV0lOMTI1MSBvciBXSU4xMjUyIEkgZ2V0IG5vdGhpbmcg aW4gdGhlIHNlY29uZCBmaWVsZCwgaXQganVzdA0KPiBpZ25vcmVzIHRoZSBk YXRhLiBTYW1lIHRoaW5nIGZvciBMQVRJTi0xLg0KPg0KPiBXaGVuIHVzaW5n IFVURjggZm9yIGNsaWVudCBlbmNvZGluZyBJIGdldCB0aGlzIG1lc3NhZ2U6 DQo+IEVSUk9SOiAgaW52YWxpZCBieXRlIHNlcXVlbmNlIGZvciBlbmNvZGlu ZyAiVVRGOCI6IDB4ZjE2ZjJjMjANCj4gQ09OVEVYVDogIENPUFkgdG1waW50 ZXJtZWRpYXRlICwgbGluZSAxDQoNCndoYXQgaXMgdGhlIGJ5dGUgKGJpbmFy eSkgZW5jb2Rpbmcgb2YgdGhlIGZpbGU/ICBpbiBoZXgsDQoNCsOxIGluIHdp bjEyNTEgID09IChubyBzdWNoIGNoYXJhY3Rlci4gICAgd2luMTI1MSBpcyBj eXJpbGxpYykNCsOxIGluIHdpbjEyNTIgID09IEYxDQrDsSBpbiBVVEYtOCAg ICA9PSBDMyBCMQ0KDQoNCg0KDQoNCiAgDQoNCg0K
On 03/23/11 5:42 PM, Nathan M. Davalos wrote: > Just in case the reply didn't go through the character in question is F1. > > I replied to the message using pgsql-bugs@postgresql.org, but I'm a wee bit new to the bug reporting stuff using the mailinglist. > > The hex of the file in total is > 32.33.30.30.30.32.09.41.6C.74.6F.20.44.65.73.65.6D.70.65.F1.6F.2C.20.53.2E.41.2E.20.44.65.20.43.2E.56.2E.0D.0A k, thats certainly win-1252. I see a tab after the 230002, then the rest of it is all one field, ending in a <CR><LF> sequence. The table you're copying this data to, what fields does it have?
QWNOdW1iZXIgY2hhcmFjdGVyIHZhcnlpbmcoMjApLA0KQWNOYW1lIGNoYXJh Y3RlciB2YXJ5aW5nKDUwKQ0KDQpUaGUgZGF0YWJhc2UgaXRzZWxmIGlzOg0K ICAgICAgIEVOQ09ESU5HID0gJ1VURjgnDQogICAgICAgVEFCTEVTUEFDRSA9 IHBnX2RlZmF1bHQNCiAgICAgICBMQ19DT0xMQVRFID0gJ0VuZ2xpc2hfVW5p dGVkIFN0YXRlcy4xMjUyJw0KICAgICAgIExDX0NUWVBFID0gJ0VuZ2xpc2hf VW5pdGVkIFN0YXRlcy4xMjUyJw0KDQoNCg0KLS0tLS1PcmlnaW5hbCBNZXNz YWdlLS0tLS0NCkZyb206IEpvaG4gUiBQaWVyY2UgW21haWx0bzpwaWVyY2VA aG9ncmFuY2guY29tXSANClNlbnQ6IFdlZG5lc2RheSwgTWFyY2ggMjMsIDIw MTEgNzo1NCBQTQ0KVG86IE5hdGhhbiBNLiBEYXZhbG9zOyBQb3N0Z3JlU1FM IEJ1Z3MNClN1YmplY3Q6IFJlOiBbQlVHU10gQlVHICM1OTQ0OiBDT1BZIEZS T00gZG9lc24ndCB3b3JrIHdpdGggaW50ZXJuYXRpb25hbCBjaGFyYWN0ZXJz DQoNCk9uIDAzLzIzLzExIDU6NDIgUE0sIE5hdGhhbiBNLiBEYXZhbG9zIHdy b3RlOg0KPiBKdXN0IGluIGNhc2UgdGhlIHJlcGx5IGRpZG4ndCBnbyB0aHJv dWdoIHRoZSBjaGFyYWN0ZXIgaW4gcXVlc3Rpb24gaXMgRjEuDQo+DQo+IEkg cmVwbGllZCB0byB0aGUgbWVzc2FnZSB1c2luZyAgcGdzcWwtYnVnc0Bwb3N0 Z3Jlc3FsLm9yZywgYnV0IEknbSBhIHdlZSBiaXQgbmV3IHRvIHRoZSBidWcg cmVwb3J0aW5nIHN0dWZmIHVzaW5nIHRoZSBtYWlsaW5nIGxpc3QuDQo+DQo+ IFRoZSBoZXggb2YgdGhlIGZpbGUgaW4gdG90YWwgaXMNCj4gMzIuMzMuMzAu MzAuMzAuMzIuMDkuNDEuNkMuNzQuNkYuMjAuNDQuNjUuNzMuNjUuNkQuNzAu NjUuRjEuNkYuMkMuMjAuNTMuMkUuNDEuMkUuMjAuNDQuNjUuMjAuNDMuMkUu NTYuMkUuMEQuMEENCg0KaywgdGhhdHMgY2VydGFpbmx5IHdpbi0xMjUyLiAg SSBzZWUgYSB0YWIgYWZ0ZXIgdGhlIDIzMDAwMiwgdGhlbiB0aGUgDQpyZXN0 IG9mIGl0IGlzIGFsbCBvbmUgZmllbGQsIGVuZGluZyBpbiBhIDxDUj48TEY+ IHNlcXVlbmNlLg0KDQoNClRoZSB0YWJsZSB5b3UncmUgY29weWluZyB0aGlz IGRhdGEgdG8sIHdoYXQgZmllbGRzIGRvZXMgaXQgaGF2ZT8NCg==
SSBqdXN0IHRyaWVkIGl0IGZyb20gdGhlIHBzcWwgY29uc29sZSBhbmQgaXQg c2VlbXMgdG8gd29yaywgd2hpY2ggaXNuJ3QgcmVhbGx5IGFuIG9wdGlvbiBm b3IgdXMgdG8gdXNlIHNpbmNlIHdlIG5lZWQgdG8gYmUgYWJsZSB0byBsZXQg ZW5kIHVzZXJzIGxvYWQgZGF0YSB2aWEgb3VyIGN1c3RvbSBkZXNpZ25lZCBk YXRhYmFzZSBhcHBsaWNhdGlvbi4gTm9uZSBvZiB0aGVtIHdvdWxkIHVuZGVy c3RhbmQgb3Iga25vdyBob3cgdG8gdXNlIHRoZSBjb25zb2xlLiBDdXJyZW50 bHkgdGhlcmUncyBubyBwcmVzc2luZyBuZWVkIHRvIGJlIGFibGUgdG8gZG8g dGhpcyBmb3IgdXMsIHNvIHdlJ3JlIGp1c3QgbG9hZGluZyB0aGUgZGF0YSB3 aXRoIGEgc2VyaWVzIG9mIGluc2VydCBzdGF0ZW1lbnRzIGluc3RlYWQgb2Yg Q09QWSBGUk9NLg0KDQp0ZXN0ZGI9IyBTRVQgQ0xJRU5UX0VOQ09ESU5HIFRP ICdXSU4xMjUyJzsNClNFVA0KdGVzdGRiPSMgY3JlYXRlIHRhYmxlIHRtcGlu dGVybWVkaWF0ZSAoYWNudW1iZXIgY2hhcmFjdGVyIHZhcnlpbmcoMjApLGFj bmFtZSBjaGFyYWN0ZXIgdmFyeWluZyg1MCkNCik7DQpDUkVBVEUgVEFCTEUN CnRlc3RkYj0jIGNvcHkgdG1waW50ZXJtZWRpYXRlIGZyb20gJ2M6XFx0ZW1w XFx0aGVmaWxlLnR4dCc7DQpXQVJOSU5HOiAgbm9uc3RhbmRhcmQgdXNlIG9m IFxcIGluIGEgc3RyaW5nIGxpdGVyYWwNCkxJTkUgMTogY29weSB0bXBpbnRl cm1lZGlhdGUgZnJvbSAnYzpcXHRlbXBcXHRoZWZpbGUudHh0JzsNCiAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICBeDQpISU5UOiAgVXNlIHRo ZSBlc2NhcGUgc3RyaW5nIHN5bnRheCBmb3IgYmFja3NsYXNoZXMsIGUuZy4s IEUnXFwnLg0KQ09QWSAxDQp0ZXN0ZGI9IyBzZWxlY3QgKiBmcm9tIHRtcGlu dGVybWVkaWF0ZTsNCiBhY251bWJlciB8ICAgICAgICAgICAgYWNuYW1lDQot LS0tLS0tLS0tKy0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQ0KIDIz MDAwMiAgIHwgQWx0byBEZXNlbXBlwrFvLCBTLkEuIERlIEMuVi4NCigxIHJv dykNCg0KVGhlIG91dHB1dCBpcyB3cm9uZyBpbiB0aGUgc2VsZWN0IHN0YXRl bWVudCBmcm9tIHRoZSBjb25zb2xlLCBidXQgYXBwZWFycyBjb3JyZWN0bHkg dXNpbmcgcGdhZG1pbjMuIEkgdGhpbmsgaXQncyBhIHByb2JsZW0gd2l0aCBv bmUgaWYgdGhlIHJlZGlzdHJpYnV0YWJsZSBsaWJyYXJpZXMgaW5zdGVhZCBv ZiBwb3N0Z3Jlc3FsIGl0c2VsZi4gV2hlbiBsb2FkaW5nIHRocm91Z2ggcGdh ZG1pbjMgb3IgZWxzZXdoZXJlIHRoZSBhY25hbWUgZmllbGQganVzdCBlbmRz IHVwIGJsYW5rIHVzaW5nIFdJTjEyNTIsIHRoZSBDT1BZIEZST00gd2lsbCBq dXN0IGlnbm9yZSB3aGF0ZXZlciBkYXRhIGlzIHN1cHBvc2VkIHRvIGJlIGxv YWRlZCBpbnRvIHRoYXQgZmllbGQgd2hlbiB0aGVyZSBpcyBhbiBpbnRlcm5h dGlvbmFsIGNoYXJhY3Rlci4NCg0KLS0tLS1PcmlnaW5hbCBNZXNzYWdlLS0t LS0NCkZyb206IHBnc3FsLWJ1Z3Mtb3duZXJAcG9zdGdyZXNxbC5vcmcgW21h aWx0bzpwZ3NxbC1idWdzLW93bmVyQHBvc3RncmVzcWwub3JnXSBPbiBCZWhh bGYgT2YgTmF0aGFuIE0uIERhdmFsb3MNClNlbnQ6IFdlZG5lc2RheSwgTWFy Y2ggMjMsIDIwMTEgNzo1OSBQTQ0KVG86IFBvc3RncmVTUUwgQnVncw0KU3Vi amVjdDogUmU6IFtCVUdTXSBCVUcgIzU5NDQ6IENPUFkgRlJPTSBkb2Vzbid0 IHdvcmsgd2l0aCBpbnRlcm5hdGlvbmFsIGNoYXJhY3RlcnMNCg0KQWNOdW1i ZXIgY2hhcmFjdGVyIHZhcnlpbmcoMjApLA0KQWNOYW1lIGNoYXJhY3RlciB2 YXJ5aW5nKDUwKQ0KDQpUaGUgZGF0YWJhc2UgaXRzZWxmIGlzOg0KICAgICAg IEVOQ09ESU5HID0gJ1VURjgnDQogICAgICAgVEFCTEVTUEFDRSA9IHBnX2Rl ZmF1bHQNCiAgICAgICBMQ19DT0xMQVRFID0gJ0VuZ2xpc2hfVW5pdGVkIFN0 YXRlcy4xMjUyJw0KICAgICAgIExDX0NUWVBFID0gJ0VuZ2xpc2hfVW5pdGVk IFN0YXRlcy4xMjUyJw0KDQoNCg0KLS0tLS1PcmlnaW5hbCBNZXNzYWdlLS0t LS0NCkZyb206IEpvaG4gUiBQaWVyY2UgW21haWx0bzpwaWVyY2VAaG9ncmFu Y2guY29tXSANClNlbnQ6IFdlZG5lc2RheSwgTWFyY2ggMjMsIDIwMTEgNzo1 NCBQTQ0KVG86IE5hdGhhbiBNLiBEYXZhbG9zOyBQb3N0Z3JlU1FMIEJ1Z3MN ClN1YmplY3Q6IFJlOiBbQlVHU10gQlVHICM1OTQ0OiBDT1BZIEZST00gZG9l c24ndCB3b3JrIHdpdGggaW50ZXJuYXRpb25hbCBjaGFyYWN0ZXJzDQoNCk9u IDAzLzIzLzExIDU6NDIgUE0sIE5hdGhhbiBNLiBEYXZhbG9zIHdyb3RlOg0K PiBKdXN0IGluIGNhc2UgdGhlIHJlcGx5IGRpZG4ndCBnbyB0aHJvdWdoIHRo ZSBjaGFyYWN0ZXIgaW4gcXVlc3Rpb24gaXMgRjEuDQo+DQo+IEkgcmVwbGll ZCB0byB0aGUgbWVzc2FnZSB1c2luZyAgcGdzcWwtYnVnc0Bwb3N0Z3Jlc3Fs Lm9yZywgYnV0IEknbSBhIHdlZSBiaXQgbmV3IHRvIHRoZSBidWcgcmVwb3J0 aW5nIHN0dWZmIHVzaW5nIHRoZSBtYWlsaW5nIGxpc3QuDQo+DQo+IFRoZSBo ZXggb2YgdGhlIGZpbGUgaW4gdG90YWwgaXMNCj4gMzIuMzMuMzAuMzAuMzAu MzIuMDkuNDEuNkMuNzQuNkYuMjAuNDQuNjUuNzMuNjUuNkQuNzAuNjUuRjEu NkYuMkMuMjAuNTMuMkUuNDEuMkUuMjAuNDQuNjUuMjAuNDMuMkUuNTYuMkUu MEQuMEENCg0KaywgdGhhdHMgY2VydGFpbmx5IHdpbi0xMjUyLiAgSSBzZWUg YSB0YWIgYWZ0ZXIgdGhlIDIzMDAwMiwgdGhlbiB0aGUgDQpyZXN0IG9mIGl0 IGlzIGFsbCBvbmUgZmllbGQsIGVuZGluZyBpbiBhIDxDUj48TEY+IHNlcXVl bmNlLg0KDQoNClRoZSB0YWJsZSB5b3UncmUgY29weWluZyB0aGlzIGRhdGEg dG8sIHdoYXQgZmllbGRzIGRvZXMgaXQgaGF2ZT8NCg0KLS0gDQpTZW50IHZp YSBwZ3NxbC1idWdzIG1haWxpbmcgbGlzdCAocGdzcWwtYnVnc0Bwb3N0Z3Jl c3FsLm9yZykNClRvIG1ha2UgY2hhbmdlcyB0byB5b3VyIHN1YnNjcmlwdGlv bjoNCmh0dHA6Ly93d3cucG9zdGdyZXNxbC5vcmcvbWFpbHByZWYvcGdzcWwt YnVncw0K
I have another example of this issue with WIN1252. This line in a copy
file:
659446 828 1 /6�\bH@^W^Za$H�\b�@\\/ <p><p></p><p>No
valid or unique HTTP objects found in XML response.</p></p>
Into this table:
Table "public.ep_tests"
Column | Type | Modifiers
---------+--------------+-----------
id | bigint | not null
v_id | integer | not null
status | character(1) | not null
vkey | text |
details | text |
Results in this error while loading the data:
the following error is encounted by the \copy: ERROR: missing data for
column "details"
CONTEXT: COPY ep_tests, line 1028752: "659446 828 1 /6�\bH@â¨"
The dump file was produced by using 9.0.3's pg_dump in text mode to dump
an 8.2 database, then using 9.0.3's psql to load the file. Both servers
are UTF8, locale WIN1252.
So it looks like we're not successfully escaping characters on WIN1252.
The characters in question are also latin characters.
We've reproduced this on a clean install.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
Josh Berkus wrote: > I have another example of this issue with WIN1252. This line in a copy > file: > > 659446 828 1 /6???\bH@^W^Za$H???\b???@\\/ <p><p></p><p>No > valid or unique HTTP objects found in XML response.</p></p> > > Into this table: > > Table "public.ep_tests" > Column | Type | Modifiers > ---------+--------------+----------- > id | bigint | not null > v_id | integer | not null > status | character(1) | not null > vkey | text | > details | text | > > Results in this error while loading the data: > > the following error is encounted by the \copy: ERROR: missing data for > column "details" > CONTEXT: COPY ep_tests, line 1028752: "659446 828 1 /6???\bH@?" > > The dump file was produced by using 9.0.3's pg_dump in text mode to dump > an 8.2 database, then using 9.0.3's psql to load the file. Both servers > are UTF8, locale WIN1252. > > So it looks like we're not successfully escaping characters on WIN1252. > The characters in question are also latin characters. > > We've reproduced this on a clean install. Has this been fixed? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Tue, Sep 6, 2011 at 12:48 PM, Bruce Momjian <bruce@momjian.us> wrote: > Josh Berkus wrote: >> I have another example of this issue with WIN1252. =A0This line in a copy >> file: >> >> 659446 =A0828 =A0 =A0 1 =A0 =A0 =A0 /6???\bH@^W^Za$H???\b???@\\/ =A0 =A0= <p><p></p><p>No >> valid or unique HTTP objects found in XML response.</p></p> >> >> Into this table: >> >> =A0 =A0 =A0Table "public.ep_tests" >> Column =A0| =A0 =A0 Type =A0 =A0 | Modifiers >> ---------+--------------+----------- >> id =A0 =A0 =A0| bigint =A0 =A0 =A0 | not null >> v_id =A0 =A0| integer =A0 =A0 =A0| not null >> status =A0| character(1) | not null >> vkey =A0 =A0| text =A0 =A0 =A0 =A0 | >> details | text =A0 =A0 =A0 =A0 | >> >> Results in this error while loading the data: >> >> the following error is encounted by the \copy: ERROR: =A0missing data for >> column "details" >> CONTEXT: =A0COPY ep_tests, line 1028752: "659446 =A0828 =A0 =A0 1 =A0 = =A0 =A0 /6???\bH@?" >> >> The dump file was produced by using 9.0.3's pg_dump in text mode to dump >> an 8.2 database, then using 9.0.3's psql to load the file. =A0Both serve= rs >> are UTF8, locale WIN1252. >> >> So it looks like we're not successfully escaping characters on WIN1252. >> =A0The characters in question are also latin characters. >> >> We've reproduced this on a clean install. > > Has this been fixed? I don't think so. It's not really clear to me what the issue is. Is there some confusion between the encoding of the file and the encoding in use in the client session? It's odd that we would have a bug that only affects win1252. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 10/19/11 5:41 AM, Robert Haas wrote: > I don't think so. It's not really clear to me what the issue is. Is > there some confusion between the encoding of the file and the encoding > in use in the client session? It's odd that we would have a bug that > only affects win1252. I think it's quite possible that this is something broken in the win1252 encoding itself. I've seen a lot of reports online for errors from other software. However, we need to at least find a workaround for users if we can't fix it ... -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com