Обсуждение: BUG #14251: COPY CSV: missing data for column that shouldn't be
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDI1MQpMb2dnZWQgYnk6ICAg ICAgICAgIE1pY2thZWwgS2VyamVhbgpFbWFpbCBhZGRyZXNzOiAgICAgIG1p Y2thZWwua2VyamVhbkBnbWFpbC5jb20KUG9zdGdyZVNRTCB2ZXJzaW9uOiA5 LjUuMApPcGVyYXRpbmcgc3lzdGVtOiAgIFVidW50dSAxNi4wNApEZXNjcmlw dGlvbjogICAgICAgIAoKSGVsbG8sDQoNCkZpcnN0IG9mIGFsbCwgdGhhbmsg eW91IGZvciBtYWtpbmcgc3VjaCBhIGNvb2wgZGF0YWJhc2UuDQoNCkknbSBo YXZpbmcgc29tZSBiYWQgdGltZSBpbnNlcnRpbmcgZGF0YSBpbiBhIGNzdiBm b3JtYXQgY29taW5nIGZyb206Cmh0dHBzOi8vd3d3Lm1heG1pbmQuY29tL2Vu L2ZyZWUtd29ybGQtY2l0aWVzLWRhdGFiYXNlDQoNClRoZSBkYXRhIGl0c2Vs ZiBjb250YWlucyBjaXRpZXMgZnJvbSBhbGwgYXJvdW5kIHRoZSB3b3JsZC4N Cg0KLSBNeSBTY2hlbWEgaXM6DQpDUkVBVEUgVEFCTEUgY2l0aWVzKA0KICAg IGNvdW50cnlfY29kZSBWQVJDSEFSKDIpLA0KICAgIGNpdHkgVkFSQ0hBUigy NTYpLA0KICAgIGNpdHlfYWNjZW50IFZBUkNIQVIoMjU2KSwNCiAgICByZWdp b24gVkFSQ0hBUigxMjgpLA0KICAgIHBvcHVsYXRpb24gRkxPQVQgREVGQVVM VCBOVUxMLA0KICAgIGxhdCBGTE9BVCwNCiAgICBsbmcgRkxPQVQNCikNCi0g UmVnYXJkaW5nIHRoZSBlbmNvZGluZyBhdCB0aGUgZmlsZToNCm1pY2thZWxA dWJ1bnR1On4kIGZpbGUgLWkgd29ybGRjaXRpZXNwb3AuY3N2DQp3b3JsZGNp dGllc3BvcC5jc3Y6IHRleHQvcGxhaW47IGNoYXJzZXQ9aXNvLTg4NTktMQ0K LSB0aGUgcXVlcnkgaXMgcmF0aGVyIHNpbXBsZToNCkNPUFkgY2l0aWVzIEZS T00gJy9ob21lL21pY2thZWwvd29ybGRjaXRpZXNwb3AuY3N2JyBERUxJTUlU RVIgJywnIENTViBIRUFERVIKRU5DT0RJTkcgJ2lzby04ODU5LTEnOw0KLSBU aGUgZXJyb3IgSSdtIGdldHRpbmc6IA0KRVJST1I6ICBtaXNzaW5nIGRhdGEg Zm9yIGNvbHVtbiAibG5nIg0KQ09OVEVYVDogIENPUFkgY2l0aWVzLCBsaW5l IDE5OTcwOgoiYWYsZGVraCJpeWtoJ3lhLERla2giaXlraCd5YSwxMywsMzQu NjAzNDUsNjkuMjQwNSINCj0+IFRoZSBjc3YgaXMgdmFsaWQgYnV0IHBvc3Rn cmVzIGRvZXNuJ3QgcmVhbGx5IGdldCBpdCByaWdodA0KDQpJIHRyaWVkIGEg ZmV3IHRoaW5nczoNCjEpIGNvbnZlcnQgdGhlIGZpbGUgdG8gdXRmOCB1c2lu ZyB0aGUgZm9sbG93aW5nIGNvbW1hbmQ6DQppY29udiAtZiBJU08tODg1OS0x IC10IFVURi04IHdvcmxkY2l0aWVzcG9wLmNzdiA+IHdvcmxkY2l0aWVzcG9w X3V0ZjguY3N2DQoyKSBnZXQgcmlkIG9mIHRoZSBoZWFkZXINCjMpIHRyeSB0 byByZWltcG9ydCB0aGUgZGF0YToNCkNPUFkgY2l0aWVzIEZST00gJy9ob21l L21pY2thZWwvd29ybGRjaXRpZXNwb3BfdXRmOC5jc3YnIERFTElNSVRFUiAn LCcKQ1NWOw0KDQpTYW1lIHJlc3VsdHMuIA0KTG9va2luZyBhdCB0aGUgZXJy b3IsIGl0IG1pZ2h0IG5vdCBiZSBpbXBvc3NpYmxlIHBvc3RncmVzIGlzIHRo aW5raW5nIHRoZSAiCmNoYXJhY3RlcnMgYXJlIHBhcnQgb2YgMSBzdHJpbmcg LT4gLT4KaHR0cHM6Ly9naXRodWIuY29tL3Bvc3RncmVzL3Bvc3RncmVzL2Js b2IvNTYyZTQ0OTcyNDkwMTk2ODg0NDUyZTYzMmEwYTZkMGRiODFiMjMzNS9z cmMvYmluL3BzcWwvdGFiLWNvbXBsZXRlLmMjTDM2MjENCg0KSSB0cmllZCB0 byBpbXBvcnQgdGhpcyBmaWxlIHVzaW5nIGV4Y2VsIGFuZCBldmVyeXRoaW5n IHdhcyB3b3JraW5nIGFzCmV4cGVjdGVkDQoNCg0KCgo=
On 7/14/2016 7:03 PM, mickael.kerjean@gmail.com wrote: > "af,dekh"iykh'ya,Dekh"iykh'ya,13,,34.60345,69.2405" > => The csv is valid but postgres doesn't really get it right the quotes don't make much sense at all. country_code = "af city = dekh"iykh'ya city_accent = Dekh"iykh'ya region = 13 population = NULL lat = 34.60345 lng = 69.2405" is dekh"iykh'ya supposed to be accented characters or something? If you don't specify a QUOTE on your COPY command, it defaults to ", this will definitely mess things up. you might try specifying a QUOTE '%' or something that doesn't exist in your data, I don't think you can disable QUOTE entirely on CSV format copies. -- john r pierce, recycling bits in santa cruz
Mickael: On Fri, Jul 15, 2016 at 4:03 AM, <mickael.kerjean@gmail.com> wrote: > I'm having some bad time inserting data in a csv format coming from: > https://www.maxmind.com/en/free-world-cities-database I've downloaded it, it is NOT in CSV format, CSV means more than commas. I've checked it does not need scaping by doing: $ perl -ne '@x=split/,/,$_; 7==@x or print "$.-$_";' ~/Downloads/worldcitiespop.txt | head And not getting any output, and checked it does not have backslashes by doing $ fgrep -c '\' ~/Downloads/worldcitiespop.txt 0 So you could probably load it by NOT using CSV ( as the .txt extension hints ), that is use FORMAT text ( the default ) and DELIMITER ',' and it will probably get in fine ( try some variations if not ). > I tried to import this file using excel and everything was working as > expected Excel lies. A lot. Maybe with good intention, but it does many things behind the scenes, like parsing non-csv data right when told to read CSV. Also, CSV is a very ill defined format, I've been unable to use it properly since the Multiplan days, specially since they started putting L10N/I18N in it. Francisco Olarte.