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 1  
t_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 1  
t_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处理。

[sql] view plain copy
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，目的也达到了。  

 

转载于:https://my.oschina.net/ruanjun/blog/817247