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

Insert Into Table1
Select id,name,age from Table2;

Table1 is our newly created table where we are dumping records from Table2 of specific columns only. We don't need to mention Table1 columns name if we are taking same columns in Select query.

Answer 2

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 3

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)