Hey guys,
I'm learning Postgresql and trying to inserts only new entries in a table and skips duplicates. I know this is a
recurrentquestion, 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
catchthe 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
insteadnothing;
-> 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
