Inserting into Postgres Multidimensional Text Array from NodeJS Knex

Question

I am attempting to insert a single row into a Postgres table from a NodeJS application using the Knex-Seed-File module for Knex.

Upon each attempt, I receive an error for only one column/field which is a multidimensional text array: photo_urls text[][] NULL,. The error states there is a malformed array literal.

Having gone through the official Postgres documentation, I've tried using double quotes:

(8.14.2. Array Value Input)

"To write an array value as a literal constant, enclose the element values within curly braces and separate them by commas...You can put double quotes around any element value, and must do so if it contains commas or curly braces."

I've also tried using ARRAY constructor syntax.

Here are various attempts Ive had at constructing the input as well as what was returned as being the actual SQL generated and the returned error:

 Attempt 1:
    array[['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']]
Result 1:
    'array[[\'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 1:
    - malformed array literal: "array[['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']]


Attempt 2:
    $${"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"}$$
Result 2:
    '"$${""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 2:
   - 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""}$$"


Attempt 3:
    ($${"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"}$$)
Result 3:
    '"($${""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 3:
    - 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""}$$)"


Attempt 4:
    array[['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"]]
Result 4:
    '"array[[\'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 4:
    - malformed array literal: ""array[['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""]]"


Attempt 5 (Post knex-seed-file upgrade):
[["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"]]

Result 5:
'"[[""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 5:
- 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""]]"

There appear to be many bugs/issues reported as related to knex postgres integration: #658, #828, #869, #1602,... which seem to have been closed and/or merged into #1661.

From what I can tell, it appears the issue was closed as resolved.

Can anyone help identify what I'm doing wrong or what I can do to resolve the issue?

touko · Accepted Answer · 2017-01-29 09:26:04Z

1

The module is now upgraded (0.3.1) and should now handle arrays properly. To enter array value after updating the package, you should use following pattern:

[["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"]]

Please open an issue at https://github.com/tohalla/knex-seed-file, upon encountering more problems.

answered Jan 29, 2017 at 9:26

touko

2,0771 gold badge12 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HendPro12 Over a year ago

I've updated the package and entered the value verbatim to what you provided. However, I'm still receiving the same error. I am now updating my description above to show the output.

Community · Accepted Answer · 2021-10-07 13:21:02Z

0

@touko correctly identified that the issue is a result of the default behavior for csv files.

When saving a csv file, double quotes are added to anything with embedded commas or double-quoted characters.

It is explained in these posts and articles:

In regards to the knex-seed-file module there is an issue opened in Github. Currently, I'm using the workaround of opening the csv file in a text editor and manually removing the undesired double quotes.

Example (note: I am using a pipe-delimited csv file):

find "[[ and replace with [[
find ]]" and replace with ]]
find "" and replace with "

edited Oct 7, 2021 at 13:21

CommunityBot

11 silver badge

answered Jan 29, 2017 at 14:53

HendPro12

1,1044 gold badges22 silver badges51 bronze badges

Collectives™ on Stack Overflow

Inserting into Postgres Multidimensional Text Array from NodeJS Knex

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related