Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.500.231\))
Subject: Re: how do I capture conflicting rows
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: 
 <CALXkTpxRGtUjH+GP67W_+fXahaUUmpTPE+LiLugF--_862PD-Q@mail.gmail.com>
Date: Mon, 15 May 2023 07:17:36 -0600
Cc: Ron <ronljohnsonjr@gmail.com>,
 Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <36F0306C-E96A-4516-B36E-E97612C95F59@elevated-dev.com>
References: 
 <CALXkTpx3iLuGRx1_4N6GJ85ptoW4n4XSdh3ChJLE-hrFGC982g@mail.gmail.com>
 <34308ba9-6033-6e57-f44e-500b2ca9cad5@gmail.com>
 <CALXkTpxRGtUjH+GP67W_+fXahaUUmpTPE+LiLugF--_862PD-Q@mail.gmail.com>
To: Nikhil Ingale <niks.bgm@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/36F0306C-E96A-4516-B36E-E97612C95F59%40elevated-dev.com>
Precedence: bulk

> On May 15, 2023, at 12:25 AM, Nikhil Ingale <niks.bgm@gmail.com> =
wrote:
>=20
> Thing is there is a list of tables (350+ tables) on which I'm running =
the insert query i.e., INSERT INTO table ON CONFLICT DO NOTHING to =
continue inserting the records by ignoring the conflicting rows. But, at =
the same time I would like to capture the conflicting rows or every =
single conflicting column (not just the PK's) and their values for every =
single table.

Create a "staging" table without constraints. Load all rows into it. =
Query for conflicts and report to user. Delete conflicting rows. Then =
INSERT INTO ... SELECT * FROM ...