search

tohalla / knex-seed-file

MIT License
4 stars 6 forks source link

Postgres Multidimensional Text Array #4

Closed HendPro12 closed 7 years ago

HendPro12 commented 7 years ago

Following upgrade to 0.3.1, I'm still unable to insert into a postgres multidimensional text array.

Attempted Insert: [["ext_profile:","ext_random:","int_random:"],["https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Ext+Profile","https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Ext+Random+1,https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Ext+Random+2","https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Int+Random+1,https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=In+Random+2,https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Int+Random+3"]]

Resulting SQL: '"[[""ext_profile:"",""ext_random:"",""int_random:""],[""https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Ext+Profile"",""https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Ext+Random+1,https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Ext+Random+2"",""https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Int+Random+1,https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=In+Random+2,https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Int+Random+3""]]"'

Error: - malformed array literal: ""[[""ext_profile:"",""ext_random:"",""int_random:""],[""https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Ext+Profile"",""https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Ext+Random+1,https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Ext+Random+2"",""https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Int+Random+1,https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=In+Random+2,https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Int+Random+3""]]"

tohalla commented 7 years ago

Unable to duplicate. Are you sure you didn't quote the whole text @HendPro12? e.g. with input "[["ext_profile:","ext_random:","int_random:"],["https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Ext+Profile","https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Ext+Random+1,https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Ext+Random+2","https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Int+Random+1,https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=In+Random+2,https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Int+Random+3"]]" I can duplicate the error, but should pass without the outmost quotes

HendPro12 commented 7 years ago

@tohalla I've pasted a screenshot of exactly how it is entered into the spreadsheet field. It should enlarge to where you can view it if you click: image

HendPro12 commented 7 years ago

@tohalla Additionally, here is how I have the column setup in my migration file: table.specificType('photo_urls', 'text[][]').nullable();

tohalla commented 7 years ago

@HendPro12 figured out where the problem is, when you export csv file from excel it will quote everything according to https://tools.ietf.org/html/rfc4180. For now, you can edit the files in text editor.

I can look more into next later this week, one option would be to pass value parser function in options, which would parse extra quotes before seeds are run.

HendPro12 commented 7 years ago

@tohalla Ok, I had noticed that excel was placing a leading apostrophe in front of the text in this cell. I tried running the seed with and without the apostrophe after I manually removed it but the results were the same either way.

I'll try editing via a text editor as you suggest.

Additionally, I see in the Postgres documentation in section 8.14.2. Array Value Input that:

Notice that the array elements are ordinary SQL constants or expressions; for instance, string literals are single quoted, instead of double quoted as they would be in an array literal.

Therefore, Im not sure but should we be enclosing all values in my sample dataset for this field in double quotes or should some of the values which dont contain commas, like those in the first array, be single quoted?

HendPro12 commented 7 years ago

@tohalla

You were correct. When I opened the file in Visual Studio Code each string for which I had placed double quotes had another set applied to it as well as a leading and trailing set around the entire field: "[[""ext_profile:"",""ext_random:"",""int_random:""],[""https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Ext+Profile"",""https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Ext+Random+1,https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Ext+Random+2"",""https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Int+Random+1,https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=In+Random+2,https://dummyimage.com/300x250/0cb3f5/fcfcfc.png&text=Int+Random+3""]]"

After manually removing everything to make it match what you originally provided me via my StackOverflow post the seed was successful.