Обсуждение: BUG #14251: COPY CSV: missing data for column that shouldn't be

Поиск
Список
Период
Сортировка

BUG #14251: COPY CSV: missing data for column that shouldn't be

От
mickael.kerjean@gmail.com
Дата:
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=

Re: BUG #14251: COPY CSV: missing data for column that shouldn't be

От
John R Pierce
Дата:
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

Re: BUG #14251: COPY CSV: missing data for column that shouldn't be

От
Francisco Olarte
Дата:
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.