CODEWITHSUNDEEP
postgresqlsql-insert
Silidrone
Silidrone

Reputation: 1591

Postgres insert from select statement with multiple values

I've checked this question but all answers seem to provide a solution for when there is a single field. I need to additionally set one more field while inserting. I tried something like:

INSERT INTO foo (x, y)
select id
FROM boo b1, 3

I get

SQL Error [42601]: ERROR: syntax error at or near "3"

How can I use the select AND another value to INSERT INTO foo?

Upvotes: 3

Views: 7847

Answers (1)

Bergi
Bergi

Reputation: 665364

You probably want

INSERT INTO foo (x, y)
SELECT b1.id, 3
FROM boo b1;

A literal value must go into the SELECT clause, it's not another table in the FROM clause. What can also work is

INSERT INTO foo (x, y)
VALUES
  ( (SELECT b1.id FROM boo b1 WHERE …), 3 );

or

INSERT INTO foo (x, y)
SELECT (SELECT b1.id FROM boo b1 WHERE …), 3;

but these are only valid if the inner SELECT (a subquery) returns exactly one value, using an appropriate WHERE condition.

Upvotes: 10

Related Questions