I'm new to pgsql, I need the do something like the "INSERT IGNORE" in mysql. After some searching I got a solution, which is adding a "do instead nothing" rule to the corresponding table, but it fails sometimes.
The table and the rule is created with the following sql statements:
create sequence ACCOUNT_ID_SEQ;
create table ACCOUNT (
ID bigint primary key default nextval('ACCOUNT_ID_SEQ'),
HOME char(255) not null,
NAME char(255)
);
create unique index on ACCOUNT(HOME);
create index on ACCOUNT(NAME);
create rule IGNORE_DUPLICATED_ACCOUNT
as on insert to ACCOUNT
where exists(select 1 from ACCOUNT where HOME = NEW.HOME)
do instead nothing;
There are about 20 clients do the following insertion (no UPDATE, some of them might DELETE):
begin transaction:
insert into ACCOUNT(HOME) values (v1);
insert into ACCOUNT(HOME) values (v2);
...
commit;
Sometimes I got the error says the unique constraint "account_home_idx" is violated.
Any suggestions?
Thanks
-Yao
