0

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?

Silidrone
  • 1,471
  • 4
  • 20
  • 35

1 Answers1

7

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.

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
  • Thank you for answering my question. The query ran but apart from this issue, I got `You will need to rewrite or cast the expression.`. Because `x` column is integer type and as far as I understand `SELECT b1.id FROM boo b1` is treated as record type. How can I solve this? – Silidrone Aug 20 '21 at 21:39
  • @Silidrone `(SELECT b1.id FROM boo b1)` should not be treated as a record, it returns a single integer column. Which of the three queries gave that error? – Bergi Aug 20 '21 at 23:05
  • The second one. The first one worked without a problem. – Silidrone Aug 21 '21 at 08:10
  • 1
    @Silidrone Ah, thanks, you're totally right - that's one pair of parenthesis too much. Repaired now ([demo](https://dbfiddle.uk/?rdbms=postgres_13&fiddle=aa223ea0b261493698bc7fa6d4275609)). I guess I've used `SELECT … FROM (VALUES (…), (…)) x` too often. – Bergi Aug 21 '21 at 13:46