To illustrate, here are some tables in Postgres 9.6:
people
id | name
----+------
1 | a
2 | b
3 | c
4 | d
groups
id | name
----+-------
10 | xxx
20 | yyy
30 | zzz
people_in_group
person_id | group_id
----------+-------
1 | 10
2 | 10
I would like to insert multiple values in people_in_group, and have the group names returned to me. I already have the person_id (2). The following works, but does not return the name.
INSERT INTO people_in_group(person_id, group_id)
SELECT '2' AS person_id, id as group_id FROM groups
WHERE name IN ('xxx', 'yyy', 'not there')
ON CONFLICT DO NOTHING
RETURNING *;
If I add name to the SELECT clause, I'll get INSERT has more expressions than target columns. Is there any way to have name from the groups table returned to me (via the RETURNING clause)? I know I pass in the group names, but the above query would fail to insert for 'xxx' (duplicate key), and 'not there' (no such group), so it would only return 'yyy'. Ideally, I'd like to be able to know why certain INSERTs failed, but I'll take what I can get.
with i as (
insert into people_in_group(person_id, group_id)
select '2' as person_id, id as group_id
from groups
where name in ('xxx', 'yyy', 'not there')
on conflict do nothing
returning *
)
select i.person_id, i.group_id, g.name
from i inner join groups g on g.id = i.group_id