PostgreSQL實現MySQL"insertignore"_MySQL

我來演示下幾種代替方法。第一種方法， 用自帶的規則（RULE）來實現。 

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。目的達到了。 

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，目的也達到了。