Обсуждение: how do I capture conflicting rows

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.

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.

On 2023-May-15, 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?

It sounds like you want a new feature:

INSERT INTO test (...) SELECT * FROM student
  ON CONFLICT EXECUTE FUNCTION do_your_stuff(excluded);

Sounds like it could be useful, but somebody would have to design it
fully and implement it.

Or maybe just

INSERT INTO test (...) SELECT * FROM student
  ON CONFLICT DO NOTHING RETURNING excluded.*;

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Now I have my system running, not a byte was off the shelf;
It rarely breaks and when it does I fix the code myself.
It's stable, clean and elegant, and lightning fast as well,
And it doesn't cost a nickel, so Bill Gates can go to hell."

> On May 15, 2023, at 12:25 AM, Nikhil Ingale <niks.bgm@gmail.com> 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
CONFLICTDO NOTHING to continue inserting the records by ignoring the conflicting rows. But, at the same time I would
liketo capture the conflicting rows or every single conflicting column (not just the PK's) and their values for every
singletable. 

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