[SQL] Skip dups on INSERT instead of generating an error ...
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: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Skip dups on INSERT instead of generating an error ...
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? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Skip dups on INSERT instead of generating an error ...
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. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Skip dups on INSERT instead of generating an error ...
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
