How to pass an array as param?

[frmoded]

Newbie to PG.

Passing the following Query works fine:
'INSERT sessions(all_programs) values(ARRAY[UUID(\'c6dc4514-8e7b-4043-9eb8-c7fd8ad2c306\')]) RETURNING *;'

Is there a way to pass the array of UUID as a param?
The following very naive attempt:

const result = (await db.query(
     'INSERT sessions(all_programs) values($1) RETURNING *;'),
     ['ARRAY[UUID(\'c6dc4514-8e7b-4043-9eb8-c7fd8ad2c306\')]']);

Results in an err.

For completeness, the table schema is:
CREATE TABLE sessions ( session_uuid UUID PRIMARY KEY DEFAULT uuid_generate_v4(), all_programs UUID ARRAY );

[sehrope]

The driver will serialize JavaScript arrays as PostgreSQL arrays. So the first element of the "params" argument to db.query(...) would be an array:

const sql = 'INSERT sessions(all_programs) values($1) RETURNING *';
const params = [
  ['c6dc4514-8e7b-4043-9eb8-c7fd8ad2c306'],
];
const result = await db.query(sql, params);

This example does not need casts as the server should be able to infer the column type but for other SQL you might need to add casts where you're using the parameters (the ::uuid[] piece).

INSERT sessions(all_programs)
  VALUES ($1::uuid[])
  RETURNING *

[frmoded]

Hmmm....

const sql = 'INSERT sessions(all_programs) values($1) RETURNING *';
const params = [
  ['c6dc4514-8e7b-4043-9eb8-c7fd8ad2c306'],
];
const result = await db.query(sql, params);

results in Trace (see bellow).

Doing the casted version yields the exact same trace.

const sql = 'INSERT sessions(all_programs) values($1::uuid[]) RETURNING *';
const params = [
      ['c6dc4514-8e7b-4043-9eb8-c7fd8ad2c303'],
];
    const result = await db.query(sql, params);```


**Trace**

server_1 | error: syntax error at or near "sessions"
server_1 | at Parser.parseErrorMessage (/usr/src/app/node_modules/pg-protocol/dist/parser.js:278:15)
server_1 | at Parser.handlePacket (/usr/src/app/node_modules/pg-protocol/dist/parser.js:126:29)
server_1 | at Parser.parse (/usr/src/app/node_modules/pg-protocol/dist/parser.js:39:38)
server_1 | at Socket. (/usr/src/app/node_modules/pg-protocol/dist/index.js:8:42)
server_1 | at Socket.emit (events.js:315:20)
server_1 | at addChunk (_stream_readable.js:302:12)
server_1 | at readableAddChunk (_stream_readable.js:278:9)
server_1 | at Socket.Readable.push (_stream_readable.js:217:10)
server_1 | at TCP.onStreamRead (internal/stream_base_commons.js:186:23)

[charmander]

INSERT → INSERT INTO

[AxelTerizaki]

I'd like to reopen this as this is exactly the issue I'm having with a simple :

DELETE FROM table WHERE id IN $1

I pass params like that :

query(myQuery, [ids])

where ids is an array of strings.

What I get in the postgres log (with all statements enabled) is kinda funny :

2021-04-19 18:53:02.304 CEST [16576] ERREUR:  erreur de syntaxe sur ou près de « $1 » au caractère 35
2021-04-19 18:53:02.304 CEST [16576] INSTRUCTION :  
	DELETE FROM kara WHERE pk_kid IN $1;

Sorry the log is in french but it's basically "syntax error on or near $1 at character 35"

It's almost as if the query wasn't being parameterized

It works if instead of passing $1 I manually do something dirty like this :

const kidList = JSON.stringify(kids).replace('[','(').replace(']',')').replace(/"/g, '\'');

I'm using node-postgres 8.6.0.

The snippet I posted above is working for me as a workaround, but it's kind of messy and I wonder why this isn't working even though it should.

[sehrope]

@AxelTerizaki The SQL syntax for foo IN (...) is for an inline values list. You can't put a parameter in place of the list. Instead, use the array comparison construct ANY:

const ids = ['foo', 'bar', 'baz'];

const sql = 'DELETE FROM table WHERE id = ANY($1::text[])';
const params = [ids];
client.query(sql, params);

[charmander]

@AxelTerizaki #1452 (comment)

DELETE FROM table WHERE id = ANY ($1)

[AxelTerizaki]

@sehrope @charmander wow, thanks for the quick replies. I learned something today :) That worked!

[valerii15298]

@sehrope @charmander @AxelTerizaki thank you. This helped me too :)