OmniShift OmniShift - 1 year ago 80
Node.js Question

postgresql queries give error when inserting arrays with strings

I have a simple query intended to insert a row into a table of my postgresql 9.5 database:

client.query('INSERT INTO "GRIDs" (idname, status, playerid, playerready) VALUES (\'' + gameRoomID + '\', 0, ARRAY[\'\',\'\',\'\',\'\'], ARRAY[0,0,0,0]);', function(err, data) {
if(err) {
throw new Error('Error inserting game room ID ' + gameRoomID);

However, for reason unknown, the string array (ARRAY[\'\',\'\',\'\',\'\']) causes the error, since if I delete it from the query along with the respective playerid column, the query works fine. I've tried the following variations, all with the same result:

ARRAY["\'\'","\'\'","\'\'","\'\'"] (surround each value with double quotes)

ARRAY["","","",""](only use double quotes)

ARRAY[,,,](just empty)

and various similar notations of the curly braces, but those seem to be annoying even with the numbered arrays, so I tend to avoid those.

What am I doing wrong?

Answer Source

If these are arrays of strings you have to declare your type.

Also either use dollar quoting or double your quotes.

 ARRAY[''''::text, '''''', ...]

Note that your database driver should be doing this escaping for you. Doing this yourself is a good recipe for sql injection issues.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download