Hey guys,
I'm learning Postgresql and trying to inserts only new entries in a table and skips duplicates. I know this is a recurrent question, but maybe you could point out what I'm doing wrong in this specific case.
Initially, I have two tables: from_t and to_t (empty);
mydb=> select * from from_t;
num
-----
1
1
2
3
4
5
5
5
(8 rows)
The basic inserts I'm trying to make is the following:
mydb=> insert into to_t (select num from from_t where num<4);
AND
mydb=> insert into to_t (select num from from_t where num>2);
To avoid duplicates, I had the following ideas:
1. put a unique constraint on num in to_t
-> problem: the first violation breaks the subquery and the remaining records are never inserted - I don't know how to catch the error in subqueries
2. create the following insert rule:
create or replace rule to_t_insert as on insert to to_t where exists (select num from to_t where num=NEW.num) do instead nothing;
-> problem below - apparently, the rule checks for unique records, but the check is not iterated as new entries are inserted. 3 is not inserted twice, but 1 and 5 are.
mydb=> insert into to_t (select num from from_t where num<4);
INSERT 0 4
mydb=> select * from to_t;
num
-----
1
1
2
3
(4 rows)
mydb=> insert into to_t (select num from from_t where num>2);
INSERT 0 4
mydb=> select * from to_t;
num
-----
1
1
2
3
4
5
5
5
(8 rows)
Could you point me to a solution?
Thanks,
Mark
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
