CODEWITHSUNDEEP
sqlpostgresql
Adzz
Adzz

Reputation: 1579

Postgres - Inserting data from another table for one column only

I am attempting the following query in Postgres 9.6.3

INSERT INTO join_table ("table_1_id", "table_2_id") VALUES
      (7, SELECT "id" from "table_2" where label='Foo Bar');

This throws ERROR: syntax error at or near "SELECT" at character 94

I have seen examples for nested selects inside of an insert statement work where only the things being selected are inserted. Is the above query even possible?

Upvotes: 6

Views: 9288

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

Use insert . . . select. values is unnecessary:

INSERT INTO join_table (table_1_id, table_2_id)
    SELECT y, "id" 
    FROM "table_2" 
    WHERE label = 'Foo Bar';

This also allows you to insert multiple rows from the other table.

Upvotes: 5

melpomene
melpomene

Reputation: 85767

Try putting parens around the sub-query:

INSERT INTO join_table ("table_1_id", "table_2_id") VALUES
      (7, (SELECT "id" from "table_2" where label='Foo Bar'));

Upvotes: 15

Related Questions