Message-ID: <3FDA3430.3010007@klaster.net>
Date: Fri, 12 Dec 2003 22:33:36 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL;
 rv:1.5) Gecko/20031007
MIME-Version: 1.0
To: "Marc G. Fournier" <scrappy@postgresql.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Skip dups on INSERT instead of generating an error ...
References: <20031212165007.L81928@ganymede.hub.org>
In-Reply-To: <20031212165007.L81928@ganymede.hub.org>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 8bit

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