[Original] PostgreSQL implements MySQL & quot; insert ignore & quot; Syntax.

Anyone familiar with MySQL may know that MySQL has an "insert ignore" syntax to ignore existing records. PostgreSQL does not currently provide such syntax, but it can be replaced by other methods.

The following shows the sample table structure and data for this demonstration.

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)

I will demonstrate several alternative methods.

The first method is implemented using the built-in 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 at this time, we insert two records, one of which has a primary key value and is ignored directly. The actual number of inserted records is 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)

Method 2: Create a trigger function that returns NULL. The function is as follows:

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'; the trigger corresponding to create function is as follows: t_girl = # create trigger tr_ib_insert_ignore before insert on insert_ignore for each row execute procedure sp_insert_ignore (); create trigger continues to insert two records. 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. The goal is achieved.

The third method is implemented.

T_girl = # insert into insert_ignorewith ytt_test (f1, f2) as (values (6, current_time), (3, current_time) select. * from ytt_test as a where. f1 not in (select id from insert_ignore as B); INSERT 0 1 view record, INSERT a record with ID 6, ignore the record with 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)

Fourth, use stored procedures instead of INSERT processing.

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; the first call throws an Error. T_girl = # select sp_insert_ignore (1, '14: 22: 35': time); NOTICE: Duplicated Key Error on ID: 1 sp_insert_ignore ---------------- (1 row) The second normal insert. 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, the goal is also achieved.

This article is from "god, let's see it !" Blog, please be sure to keep this source http://yueliangdao0608.blog.51cto.com/397025/1352270