PostgreSQL implements MySQL & amp; quot; insertignore & amp; 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.
T_girl = # \ d insert_ignore Table "ytt. insert_ignore "Column | Type | Modifiers ---------- + response + ----------- 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 built-in Rules (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 ignore it directly. The actual number of inserted records is 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)

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.

T_girl = # insert into insert_ignore with 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.