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?
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.
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?
(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?