Re: how do I capture conflicting rows

[Rui DeSousa <rui@xxxxxxxxxxxxx>]

On May 15, 2023, at 1:32 AM, Nikhil Ingale <niks.bgm@xxxxxxxxx> 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

;