How to use the output of select query as an input in the insert query?

Question

I have following two tables:-

postgres=# select * from district;
 id |   name
----+-----------
  1 | Ahmedabad
  2 | Barmer
(2 rows)

postgres=# select * from warehouse;
 id | name | district_id
----+------+-------------
(0 rows)

I am referring district table from warehouse. Now I want to insert into warehouse. I am using following query

postgres=# insert into warehouse
(name, district_id)
values
('Ghodasar-WH', select id from district where name = 'Ahmedabad');
ERROR:  syntax error at or near "select"
LINE 4: ('Ghodasar-WH', select id from district where name = 'Ahmeda...

But it gives me error, as shown above. Why I can't use the result of another select query in the insert query, as I am doing in above query ? I think, what I am doing is a valid scenario. Is there any limitations, that's preventing it from a valid case ?

Thanks in advance.

Gordon Linoff · Accepted Answer · 2018-04-04 11:07:51Z

6

Vao Tsun has the correct answer for using insert . . . select (and duly upvoted).

However, you are trying to use a subquery in values(). That is allowed, but a subquery needs its own parentheses. So your version would work as:

insert into warehouse (name, district_id)
    values ( 'Ghodasar-WH', (select id from district where name = 'Ahmedabad') );

answered Apr 4, 2018 at 11:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Vao Tsun Over a year ago

I did not propose subquery, to avoid possible more than one row returned by a subquery used as an expression, but now I think - maybe your way is better, so user sees that he has multiple rows, while mine will silently accept all returned rows

Vao Tsun · Accepted Answer · 2018-04-04 11:03:04Z

5

try:

insert into warehouse
(name, district_id)
select 'Ghodasar-WH',id from district where name = 'Ahmedabad';

https://www.postgresql.org/docs/current/static/sql-insert.html

{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }

so just use query here

answered Apr 4, 2018 at 11:03

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

How to use the output of select query as an input in the insert query?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related