INSERT SQL statement inside a SELECT SQL statement in PostgreSQL

Question

Can someone tell me why this doesn't work?

select * 
from keyword_groups_list 
where group_id = (insert into keyword_groups_list(group_name, keyword_tool_id) 
                  values('title', 86574551) returning group_id);

I get an error in PostgreSQL:

ERROR: Syntax error at or near "into"
LINE 1: ...from keyword_groups_list where group_id = (insert into keywo...

you can't use an insert for returning a value to compare .. explain better your goal .. — ScaisEdge
– ScaisEdge, Commented Sep 7, 2018 at 18:16
first I am inserting a row in Postgres and after insertion, I am returning group_id of that inserted row. This group id will be used to insert into another table. But I am getting this error — Narendra Kumawat
– Narendra Kumawat, Commented Sep 7, 2018 at 18:20
Removed my comment @a_horse_with_no_name I was way off base on that one. Your CTE is beautiful, BTW and I'm totally going to steal that at some point in the future. — JNevill
– JNevill, Commented Sep 7, 2018 at 18:22

user330315 · Accepted Answer · 2018-09-07 18:19:51Z

5

I don't see why you would need the select at all. Just use returning * to get all columns of the newly inserted rows:

insert into keyword_groups_list(group_name, keyword_tool_id) 
values ('title', 86574551) 
returning *;

Having said that, you can however do this with a CTE, if you really need a select:

with new_groups as (
    insert into keyword_groups_list(group_name, keyword_tool_id) 
    values ('title', 86574551) 
    returning group_id
)
select * 
from keyword_groups_list 
where group_id in (select group_id from new_groups);

But I can't think of any reason to prefer that over the simple returning *;

answered Sep 7, 2018 at 18:19

user330315

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

2 Comments

JNevill Over a year ago

That INSERT CTE is a nice trick. I'm going to remember that one!

Narendra Kumawat Over a year ago

while running this sql statements, it returned empty row.

Brad · Accepted Answer · 2018-09-07 18:26:08Z

0

You can access that value if you put it into a CTE

See this SQL fiddle

with t as (
    insert into mytable (id, val) values(1,'abc') returning id
)
select * from anotherTable where id in (select id from t)

The Postgres INSERT documentation has another CTE example like this too. Search for "Returning"

Or just simplify to

insert into mytable (id, val) values(1,'abc') returning *

answered Sep 7, 2018 at 18:26

Brad

12.4k4 gold badges51 silver badges82 bronze badges

Collectives™ on Stack Overflow

INSERT SQL statement inside a SELECT SQL statement in PostgreSQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related