From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: how do I capture conflicting rows
Date: Mon, 15 May 2023 01:42:46 -0500
Message-ID: <8a13ca66-0a65-2a3c-f1e3-8736dd30dfe1@gmail.com> (raw)
In-Reply-To: <CALXkTpxRGtUjH+GP67W_+fXahaUUmpTPE+LiLugF--_862PD-Q@mail.gmail.com>
References: <CALXkTpx3iLuGRx1_4N6GJ85ptoW4n4XSdh3ChJLE-hrFGC982g@mail.gmail.com>
<34308ba9-6033-6e57-f44e-500b2ca9cad5@gmail.com>
<CALXkTpxRGtUjH+GP67W_+fXahaUUmpTPE+LiLugF--_862PD-Q@mail.gmail.com>
Maybe you can get something for nothing, but I'm dubious; you're going to
have to pay a price /somewhere/.
On 5/15/23 01:25, Nikhil Ingale wrote:
> 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.
>
> INSERT INTO TABLE ON CONFLICT DO NOTHING don't even report us on the
> conflicting rows. How do I identify what the conflicting rows are by
> continuing the inserts to happen even if there are any conflicts. I mean
> my insert command shouldn't fail on conflicts but at the same time
> conflicts should be reported to the user.
>
> Regards,
> Nik
>
> On Mon, May 15, 2023 at 11:39 AM Ron <ronljohnsonjr@gmail.com> wrote:
>
> On 5/15/23 00:32, Nikhil Ingale wrote:
> > Hi All,
> >
> > The following query inserts the rows by ignoring the rows that has
> conflicts.
> >
> > 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?
>
> On conflict insert the PK into a separate table, along with a timestamp
> column populated by clock_timestamp(). (That way you can export and
> delete
> sets of records while it's being written to.
>
> --
> Born in Arizona, moved to Babylonia.
>
>
--
Born in Arizona, moved to Babylonia.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: how do I capture conflicting rows
In-Reply-To: <8a13ca66-0a65-2a3c-f1e3-8736dd30dfe1@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox