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
);
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 *
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.
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)
```
INSERT → INSERT INTO
Most helpful comment
INSERT→INSERT INTO