Postgres: insert value from another table as part of multi-row insert?

Question

I am working in Postgres 9.6 and would like to insert multiple rows in a single query, using an INSERT INTO query.

I would also like, as one of the values inserted, to select a value from another table.

This is what I've tried:

insert into store_properties (property, store_id) 
values 
  ('ice cream', select id from store where postcode='SW1A 1AA'),
  ('petrol', select id from store where postcode='EC1N 2RN')
;

But I get a syntax error at the first select. What am I doing wrong?

Note that the value is determined per row, i.e. I'm not straightforwardly copying over values from another table.

S-Man · Accepted Answer · 2019-07-24 15:15:35Z

0

demo:db<>fiddle

insert into store_properties (property, store_id) 
values 
('ice cream', (select id from store where postcode='SW1A 1AA')),
('petrol', (select id from store where property='EC1N 2RN'))

There were some missing braces. Each data set has to be surrounded by braces and the SELECT statements as well.

I don't know your table structure but maybe there is another error: The first data set is filtered by a postcode column, the second one by a property column...

answered Jul 24, 2019 at 15:15

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

1 Comment

S-Man Over a year ago

@Richard You're welcome: I would appreciate if you could upvote as well

Collectives™ on Stack Overflow

Postgres: insert value from another table as part of multi-row insert?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related