Re: how do I capture conflicting rows

> On May 15, 2023, at 1:32 AM, Nikhil Ingale <niks(dot)bgm(at)gmail(dot)com> wrote:
>
> INSERT INTO test (id,name,age,branch) SELECT * FROM student ON CONFLICT DO NOTHING;
>
> How do I capture the conflicting records to a file while non conflicting records are inserted to the table?
>

You can return the rows inserted and from that you can determine which rows had conflicts by returning the inserted rows.

with x (id, name, age, branch) as (
select id, name, age, branch
from student
), insrt (id) as (
insert into test (id,name,age,branch)
select id, name, age, branch from x
on conflict do nothing
returning id
)
select x.*
from x
left out join insrt on insrt.id = x.id
where insrt.id is null
;