Postgres - Inserting data from another table for one column only

Question

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?

Why do you need to use double quotes?

Abdel P.
– Abdel P.

2017-11-19 13:01:13 +00:00
Commented Nov 19, 2017 at 13:01 — Abdel P.
– Abdel P., Commented Nov 19, 2017 at 13:01

melpomene · Accepted Answer · 2017-11-19 12:57:26Z

15

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'));

answered Nov 19, 2017 at 12:57

melpomene

86.2k8 gold badges96 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adzz Over a year ago

Ahh I knew it would be something simple! Thank you.

Gordon Linoff · Accepted Answer · 2017-11-19 13:08:43Z

5

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.

answered Nov 19, 2017 at 13:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Postgres - Inserting data from another table for one column only

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related