Postgres INSERT INTO with SELECT

Question

I'm trying to write some SQL to insert records into a PG table.

This is the logic:

• For every record in the costprojects table that has coststatus_id=1
• insert a new record into costestimates table
  • costcat_id=30, amount=0, costproject_id=costproject.id(from the costprojects record) , maintenance=‘FALSE’, position=22

This is the SQL code I tried:

INSERT INTO costestimates (costcat_id, amount, costproject_id, maintenance, position) VALUES (30, 0, costproject.id, false, 22)
(SELECT id FROM costprojects WHERE coststatus_id=1)

I get ERROR: syntax error at or near "("

Answer 1

It should be something like this:

INSERT INTO costestimates (costcat_id, amount, costproject_id, maintenance, position)
SELECT 30, 0, id, false, 22 FROM costprojects WHERE coststatus_id=1;

See postgres INSERT syntax

Answer 2

Your syntax is a bit off; what you want is to actually compose the VALUES list using your SELECT statement.

Try something like this:

INSERT INTO
  costestimates (costcat_id, amount, costproject_id, maintenance, position)
  (SELECT 30, 0, id, false, 22 FROM costprojects WHERE coststatus_id=1)