pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedhow do I capture conflicting rows
8+ messages / 6 participants
[nested] [flat]
* how do I capture conflicting rows
@ 2023-05-15 05:32 Nikhil Ingale <niks.bgm@gmail.com>
0 siblings, 3 replies; 8+ messages in thread
From: Nikhil Ingale @ 2023-05-15 05:32 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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?
Regards,
Nikhil Ingale
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how do I capture conflicting rows
@ 2023-05-15 06:09 Ron <ronljohnsonjr@gmail.com>
parent: Nikhil Ingale <niks.bgm@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Ron @ 2023-05-15 06:09 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how do I capture conflicting rows
@ 2023-05-15 06:25 Nikhil Ingale <niks.bgm@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Nikhil Ingale @ 2023-05-15 06:25 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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.
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how do I capture conflicting rows
@ 2023-05-15 06:42 Ron <ronljohnsonjr@gmail.com>
parent: Nikhil Ingale <niks.bgm@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Ron @ 2023-05-15 06:42 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how do I capture conflicting rows
@ 2023-05-15 11:15 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: Nikhil Ingale <niks.bgm@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Alvaro Herrera @ 2023-05-15 11:15 UTC (permalink / raw)
To: Nikhil Ingale <niks.bgm@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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."
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how do I capture conflicting rows
@ 2023-05-15 13:17 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Nikhil Ingale <niks.bgm@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Scott Ribe @ 2023-05-15 13:17 UTC (permalink / raw)
To: Nikhil Ingale <niks.bgm@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
> 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 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 ...
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how do I capture conflicting rows
@ 2023-05-15 13:58 Mukesh Rajpurohit <vivasvan1902@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 8+ messages in thread
From: Mukesh Rajpurohit @ 2023-05-15 13:58 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Nikhil Ingale <niks.bgm@gmail.com>; Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi Nikhil,
After loading data in the target table query...
insert into temptable_conflictingrows (select * from sourcetable except
select * from targettable_nonconflictingrows);
Thanks
Mukesh
On Mon, May 15, 2023 at 6:48 PM Scott Ribe <scott_ribe@elevated-dev.com>
wrote:
> > 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 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 ...
>
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how do I capture conflicting rows
@ 2023-05-15 14:53 Rui DeSousa <rui@crazybean.net>
parent: Nikhil Ingale <niks.bgm@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Rui DeSousa @ 2023-05-15 14:53 UTC (permalink / raw)
To: Nikhil Ingale <niks.bgm@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> 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 = x.id
where insrt.id is null
;=
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-05-15 14:53 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-15 05:32 how do I capture conflicting rows Nikhil Ingale <niks.bgm@gmail.com>
2023-05-15 06:09 ` Ron <ronljohnsonjr@gmail.com>
2023-05-15 06:25 ` Nikhil Ingale <niks.bgm@gmail.com>
2023-05-15 06:42 ` Ron <ronljohnsonjr@gmail.com>
2023-05-15 13:17 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-05-15 13:58 ` Mukesh Rajpurohit <vivasvan1902@gmail.com>
2023-05-15 11:15 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-05-15 14:53 ` Rui DeSousa <rui@crazybean.net>
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