PostgreSQL 實現MySQL "insert ignore" 文法。

對MySQL熟悉的人可能都知道，MySQL 有一個“insert ignore" 文法來忽略已經存在的記錄。 PostgreSQL暫時不提供這樣的文法，但是可以用其他方法來代替。


以下為本次示範的範例表結構以及資料。

t_girl=# \d insert_ignore                                    Table "ytt.insert_ignore"  Column  |          Type          | Modifiers ----------+------------------------+----------- id       | integer                | not null log_time | time without time zone | Indexes:    "insert_ignore_pkey" PRIMARY KEY, btree (id)t_girl=# select * from insert_ignore; id |    log_time    ----+----------------  1 | 14:44:12.37185(1 row)

我來示範下幾種代替方法。

第一種方法， 用內建的規則（RULE）來實現。

t_girl=# create rule r_insert_ignore as on insert to insert_ignore where exists (select 1 from insert_ignore where id = new.id) do instead nothing;    CREATE RULE這時，我們插入兩條記錄，其中一條的主索引值已經存在，直接忽略掉。 實際插入的記錄數為1.t_girl=# insert into insert_ignore values(1,current_time),(2,current_time);INSERT 0 1t_girl=# select * from insert_ignore; id |    log_time     ----+-----------------  1 | 14:44:12.37185  2 | 14:48:22.222848(2 rows)

第二種方法， 建立一個返回NULL的觸發器函數。 那麼函數體如下：

t_girl=# create or replace function sp_insert_ignore() returns trigger as  $ytt$ begin   perform  1 from insert_ignore where id = new.id;   if found then     return null;  end if;  return new;end;$ytt$ language 'plpgsql';CREATE FUNCTION對應的觸發器如下：t_girl=# create trigger tr_ib_insert_ignore before insert on insert_ignore for each row execute procedure sp_insert_ignore();CREATE TRIGGER繼續插入兩條記錄。t_girl=# insert into insert_ignore values (3,current_time),(2,current_time);INSERT 0 1t_girl=# select * from insert_ignore; id |    log_time     ----+-----------------  1 | 14:44:12.37185  2 | 14:48:22.222848  3 | 15:05:33.198847(3 rows)OK。目的達到了。

第三種方法，用WITH來實現。

t_girl=# insert into insert_ignore with ytt_test(f1,f2) as (values(6,current_time),(3,current_time)) select a.* from ytt_test as a where a.f1 not in (select id from insert_ignore as b);                          INSERT 0 1查看記錄，插入了一條ID為6的記錄，忽略了ID為3的記錄。t_girl=# select * from insert_ignore; id |    log_time     ----+-----------------  1 | 14:44:12.37185  2 | 14:48:22.222848  3 | 15:05:33.198847  6 | 15:15:52.297802(4 rows)

第四種，用預存程序來代替INSERT處理。

t_girl=# create or replace function sp_insert_ignore (IN f_id int,IN f_log_time time without time zone) returns void as $ytt$begin  insert into insert_ignore values (f_id,f_log_time);  exception   when unique_violation  then    raise notice 'Duplicated Key Error on ID:%',f_id;    return;end;$ytt$ language plpgsql;第一次調用，拋出了錯誤。t_girl=# select sp_insert_ignore(1,'14:22:35'::time); NOTICE:  Duplicated Key Error on ID:1 sp_insert_ignore ------------------ (1 row)第二次正常插入。t_girl=# select sp_insert_ignore(8,'14:22:35'::time); sp_insert_ignore ------------------ (1 row)t_girl=# select * from insert_ignore; id |    log_time     ----+-----------------  1 | 14:44:12.37185  2 | 14:48:22.222848  3 | 15:05:33.198847  6 | 15:15:52.297802  8 | 14:22:35(5 rows)t_girl=# OK，目的也達到了。