INSERT only unique records
От | Mark Felegyhazi |
---|---|
Тема | INSERT only unique records |
Дата | |
Msg-id | 1247.13639.qm@web54409.mail.yahoo.com обсуждение исходный текст |
Ответы |
Re: INSERT only unique records
Re: INSERT only unique records |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: