Insert with returning a subquery

Question

I am trying to insert a record in an m:n table (User-Group Relation) and return the group when the user successfully joined.

But I can't manage to return the whole group after the insert.

with "group" as (
    SELECT * from "group" where code = 'tohubo' LIMIT 1
)
insert into group_users__user_groups ("group_users", "user_groups")
    select id from "group", 1 
returning (SELECT * from "group")

With that query I currently get the error message

subquery must return only one column

I also tried to just return *, but then I only get the content of group_users__user_groups.

I also tried to add an additional Select at the end:

with "found_group" as (
    SELECT * from "group" where code = 'tohubo' LIMIT 1
)
insert into group_users__user_groups ("group_users", "user_groups")
    select 1, id from "found_group";
Select * from "found_group";

But then the WITH part is not defined in the second query:

Kernel error: ERROR: relation "found_group" does not exist

in Oracle we need to write Insert first then with and then select from table or inline table name. Check once whether you also need to make similar changes. — pratik garg
– pratik garg, Commented May 11, 2016 at 8:35
select id from "group", 1 this part is not clear - are you trying to join "group" with 1?.. cos if you want to insert two values it should be select id,1 from "group" — Vao Tsun
– Vao Tsun, Commented May 11, 2016 at 8:39
btw postgresql.org/docs/9.5/static/sql-insert.html so was not meant to return those two column on returning *?.. — Vao Tsun
– Vao Tsun, Commented May 11, 2016 at 8:40

user330315 · Accepted Answer · 2016-05-11 08:44:33Z

8

The returning clause can only return data that was affected by the insert.

And you can only have one "final" statement in a CTE, not an insert and a select.

But you can simply move the insert into a second cte, and then have a single SELECT at the end that returns the data that was found

with found_group as (
    SELECT * 
    from "group" 
    where code = 'tohubo' 
    LIMIT 1
), inserted as (
   insert into group_users__user_groups (group_users, user_groups)
   select 1, id 
   from found_group
)
Select * 
from found_group;

answered May 11, 2016 at 8:44

user330315

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

1 Comment

Darth Over a year ago

How can i return inserted group_users value (= 1) in final select * from found_group?

Collectives™ on Stack Overflow

Insert with returning a subquery

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