using a subquery as a value in node-postgres

Question

I need to perform something like this:

pg.query(`insert into tbl (field1, field2) values($1, $2)`, ["a", "(select id from another_table limit 1)"])

this causes the following error

error: invalid input syntax for type uuid: "(select id from another_table limit 1)"

The subquery must be part of the query, not of your Node-code. Rewriting the VALUES-part to use a SELECT might be cleaner: INSERT INTO tbl (field1, field2) SELECT $1, id FROM another_table LIMIT 1;. This also fixes the other syntax error in your SQL, the comma right before , values(. — Frank Heikens
– Frank Heikens, Commented Oct 25, 2023 at 15:27
ok, what does the fill query look like inside node-postgres? @FrankHeikens — Sherif eldeeb
– Sherif eldeeb, Commented Oct 25, 2023 at 15:46
solved by pg.query(INSERT INTO tbl (field1, field2) values ($1, $2) , ["'a'", "SELECT $1, id FROM another_table LIMIT 1"]) note that the subquery is not wrapped by single quoyes — Sherif eldeeb
– Sherif eldeeb, Commented Oct 26, 2023 at 6:30

Frank Heikens · Accepted Answer · 2023-10-25 15:52:32Z

1

Rewrite the INSERT to use a SELECT:

pg.query(`INSERT INTO tbl (field1, field2) SELECT $1, id FROM another_table LIMIT 1;`, ["a"])

However, a LIMIT without an ORDER BY is rather strange and can lead to unexpected results.

answered Oct 25, 2023 at 15:52

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

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

1 Answer 1