Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pyZZe-0004hG-BX for pgsql-admin@arkaria.postgresql.org; Mon, 15 May 2023 14:53:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pyZZd-0005ns-1s for pgsql-admin@arkaria.postgresql.org; Mon, 15 May 2023 14:53:17 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pyZZc-0005ig-94 for pgsql-admin@lists.postgresql.org; Mon, 15 May 2023 14:53:16 +0000 Received: from impout007aa.msg.chrl.nc.charter.net ([47.43.20.31] helo=impout007.msg.chrl.nc.charter.net) by makus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1pyZZU-002TLH-SU for pgsql-admin@lists.postgresql.org; Mon, 15 May 2023 14:53:14 +0000 Received: from cerberus.crazybean.net ([71.11.29.254]) by cmsmtp with ESMTP id yZZTp6zFBMkkhyZZTpr4Th; Mon, 15 May 2023 14:53:08 +0000 Authentication-Results: crazybean.net; none X-Authority-Analysis: v=2.4 cv=P/gpOwMu c=1 sm=1 tr=0 ts=64624754 a=GkUsAQdcAzAk0MT19QAIRQ==:117 a=GkUsAQdcAzAk0MT19QAIRQ==:17 a=xqWC_Br6kY4A:10 a=P0xRbXHiH_UA:10 a=pGLkceISAAAA:8 a=jY14wI_Ep1qHrHOOJH8A:9 a=CjuIK1q_8ugA:10 a=CvfR4DZeAAAA:8 a=B5XSDwt2AAAA:8 a=rufmfXTPz5gx5IcdOpoA:9 a=EK6dJczGk0PKsqHj:21 a=_W_S_7VecoQA:10 a=lqcHg5cX4UMA:10 a=tZd2iOn18uB6oPaJl2p9:22 a=NuscrIHVwPG05b-V1dNI:22 Received: from smtpclient.apple ( [2001:470:e117:4:69ba:559a:ce86:5b49]) by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id b4b3a6cd (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO); Mon, 15 May 2023 10:53:03 -0400 (EDT) From: Rui DeSousa Message-Id: <6D26434B-74A9-43FF-B593-07DAD0BBFD9E@crazybean.net> Content-Type: multipart/alternative; boundary="Apple-Mail=_379162B6-4CFD-48BE-B387-B849F07E0391" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.120.41.1.3\)) Subject: Re: how do I capture conflicting rows Date: Mon, 15 May 2023 10:53:03 -0400 In-Reply-To: Cc: pgsql-admin@lists.postgresql.org To: Nikhil Ingale References: X-Mailer: Apple Mail (2.3696.120.41.1.3) X-CMAE-Envelope: MS4xfB58fj4jNR1xchgQZ7hHwNkqWTypUOySH2rgVXP+SEj2ZOL2HSUSuR/FZ14nYbpogfrruQuMUPwqO6GAQw6dMhg0LQrHe7EqU4r4JN/5Jp2xkoyJ2DwT s/nDzw+iSjCAn1ASCt1z23u41L3QMZ0rotes2NKK8SWRy+cCYyTnDmkmKZxj4ZZunw/BVdr01KBXYumbLxOldrvfiW37uKSMTcCnPG2TtujOWFtI8rr3wQYd lClM4Ge+NfY0AarBp5Lshw== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_379162B6-4CFD-48BE-B387-B849F07E0391 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii > On May 15, 2023, at 1:32 AM, Nikhil Ingale wrote: >=20 > INSERT INTO test (id,name,age,branch) SELECT * FROM student ON = CONFLICT DO NOTHING; >=20 > How do I capture the conflicting records to a file while non = conflicting records are inserted to the table? >=20 You can return the rows inserted and from that you can determine which = rows had conflicts by returning the inserted rows.=20 with x (id, name, age, branch) as ( select id, name, age, branch from student=20 ), insrt (id) as ( insert into test (id,name,age,branch)=20 select id, name, age, branch from x=20 on conflict do nothing=20 returning id ) select x.* from x left out join insrt on insrt.id =3D x.id where insrt.id is null ;= --Apple-Mail=_379162B6-4CFD-48BE-B387-B849F07E0391 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii

On May 15, 2023, at 1:32 AM, Nikhil Ingale <niks.bgm@gmail.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 =3D x.id
where insrt.id is null
;
= --Apple-Mail=_379162B6-4CFD-48BE-B387-B849F07E0391--