pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSkip dups on INSERT instead of generating an error ...
4+ messages / 3 participants
[nested] [flat]
* Skip dups on INSERT instead of generating an error ...
@ 2003-12-12 20:53 Marc G. Fournier <scrappy@postgresql.org>
2003-12-12 21:02 ` Re: Skip dups on INSERT instead of generating an error ... scott.marlowe <scott.marlowe@ihs.com>
2003-12-12 21:03 ` Re: Skip dups on INSERT instead of generating an error ... scott.marlowe <scott.marlowe@ihs.com>
2003-12-12 21:33 ` Re: Skip dups on INSERT instead of generating an error ... Tomasz Myrta <jasiek@klaster.net>
0 siblings, 3 replies; 4+ messages in thread
From: Marc G. Fournier @ 2003-12-12 20:53 UTC (permalink / raw)
To: pgsql-sql
I need to be able to run an INSERT INTO / SELECT FROM UNION which combines
two tables into one ... *but* ... the INTO table has a primary key on the
first column, so if the result of the UNION generates dups, by default, of
course, it will generate errors ... what I'd like is to have it so that it
just skips over those records.
First thought would be to write a quite plpgsql function that would do a
SELECT first, to see if the value already exists, and if not, then do the
INSERT ... but am wondering if maybe there is a cleaner way that I'm not
thinking of?
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Skip dups on INSERT instead of generating an error ...
2003-12-12 20:53 Skip dups on INSERT instead of generating an error ... Marc G. Fournier <scrappy@postgresql.org>
@ 2003-12-12 21:02 ` scott.marlowe <scott.marlowe@ihs.com>
2 siblings, 0 replies; 4+ messages in thread
From: scott.marlowe @ 2003-12-12 21:02 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@postgresql.org>; +Cc: pgsql-sql
On Fri, 12 Dec 2003, Marc G. Fournier wrote:
>
> I need to be able to run an INSERT INTO / SELECT FROM UNION which combines
> two tables into one ... *but* ... the INTO table has a primary key on the
> first column, so if the result of the UNION generates dups, by default, of
> course, it will generate errors ... what I'd like is to have it so that it
> just skips over those records.
>
> First thought would be to write a quite plpgsql function that would do a
> SELECT first, to see if the value already exists, and if not, then do the
> INSERT ... but am wondering if maybe there is a cleaner way that I'm not
> thinking of?
I thought unions, by definition, couldn't create dups, unless you used the
all keyword... OR do you just mean that you have dup pks, not the whole
row?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Skip dups on INSERT instead of generating an error ...
2003-12-12 20:53 Skip dups on INSERT instead of generating an error ... Marc G. Fournier <scrappy@postgresql.org>
@ 2003-12-12 21:03 ` scott.marlowe <scott.marlowe@ihs.com>
2 siblings, 0 replies; 4+ messages in thread
From: scott.marlowe @ 2003-12-12 21:03 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@postgresql.org>; +Cc: pgsql-sql
On Fri, 12 Dec 2003, Marc G. Fournier wrote:
>
> I need to be able to run an INSERT INTO / SELECT FROM UNION which combines
> two tables into one ... *but* ... the INTO table has a primary key on the
> first column, so if the result of the UNION generates dups, by default, of
> course, it will generate errors ... what I'd like is to have it so that it
> just skips over those records.
>
> First thought would be to write a quite plpgsql function that would do a
> SELECT first, to see if the value already exists, and if not, then do the
> INSERT ... but am wondering if maybe there is a cleaner way that I'm not
> thinking of?
Crap, just read what you actually wrote. Hmmm. I'd guess a trigger on
the table might be able to do it, but performance is gonna suck.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Skip dups on INSERT instead of generating an error ...
2003-12-12 20:53 Skip dups on INSERT instead of generating an error ... Marc G. Fournier <scrappy@postgresql.org>
@ 2003-12-12 21:33 ` Tomasz Myrta <jasiek@klaster.net>
2 siblings, 0 replies; 4+ messages in thread
From: Tomasz Myrta @ 2003-12-12 21:33 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@postgresql.org>; +Cc: pgsql-sql
Dnia 2003-12-12 21:53, Użytkownik Marc G. Fournier napisał:
> I need to be able to run an INSERT INTO / SELECT FROM UNION which combines
> two tables into one ... *but* ... the INTO table has a primary key on the
> first column, so if the result of the UNION generates dups, by default, of
> course, it will generate errors ... what I'd like is to have it so that it
> just skips over those records.
>
> First thought would be to write a quite plpgsql function that would do a
> SELECT first, to see if the value already exists, and if not, then do the
> INSERT ... but am wondering if maybe there is a cleaner way that I'm not
> thinking of?
What kind of dups are you talking about? UNION eliminates duplicates by
default as described in documentation.
If you want to eliminate only primary key duplicates, you can use
distinct and subselects:
insert into table3
select distinct on (some_id) * from
(select * from table1 union
select * from table2) x;
Another way to eliminate such duplicates is creating simple pl/pgsql
insert trigger which checks dups before inserting new rows and returns
NULL if some row already exists.
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-12-12 21:33 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-12 20:53 Skip dups on INSERT instead of generating an error ... Marc G. Fournier <scrappy@postgresql.org>
2003-12-12 21:02 ` scott.marlowe <scott.marlowe@ihs.com>
2003-12-12 21:03 ` scott.marlowe <scott.marlowe@ihs.com>
2003-12-12 21:33 ` Tomasz Myrta <jasiek@klaster.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