database design - How to use triggers to prevent duplicate records in PostgreSQL? -

i wish create stored procedure (in plpgsql, postgresql 9.1) first checks sure record going inserted unique on 4 of columns, or if record updated, updated unique values.

  example:     record (1,2,3,4) inserted.     if record (1,2,3,4) exists, not insert duplicate record.     if record (1,2,3,4) not exist, insert it.      record (1,2,3,4) updated (5,6,7,8).     if record (5,6,7,8) exists, not update record. (duplicate record not allowed).     if record (5,6,7,8) not exist, update record new values. 

i had used unique index on record's fields, learn how trigger written accomplish this.

i had used unique index on record's fields, learn how trigger written accomplish this.

this misunderstanding. if set of columns supposed unique, use unique constraint (or make pk) in case. , aware of special role null values:

• composite primary key enforces not null constraints on involved columns
• create unique constraint null columns

the rest of answer largely outdated. since postgres 9.5 added upsert there simpler solution:

insert tbl (col1, col2, col3, col4) values (1, 2, 3, 4) on     conflict on constraint my_4_col_uni nothing; 

the rest postgres 9.4 or older

triggers can help enforce constraint. fail enforce uniqueness on own due inherent race conditions.

you can let unique constraint handle duplicate keys. you'll exception violations. avoid exceptions most of time¹ can use simple trigger:

create or replace function tbl_ins_up_before()   returns trigger $func$ begin  if exists (select 1 tbl            (col1,     col2,     col3,     col4)            = (new.col1, new.col2, new.col3, new.col4))    return null; end if;  return new;  end $func$  language plpgsql;  create trigger ins_up_before before insert or update of col1, col2, col3, col4  -- fire when relevant on tbl each row execute procedure tbl_ins_up_before(); 

¹there inherent race condition in time slice between checking if row exists , inserting row, cannot avoided unless lock table exclusively (very expensive). details depend on exact definition of constraint (may deferrable). might still exception if concurrent transaction finds (at virtually same moment) (1,2,3,4) not there yet , inserts before you. or operation might aborted, existing row deleted before can commit.

this cannot fixed row-level locking either, because cannot lock rows aren't there yet (predicate locking) in postgres version 9.6.

you need unique constraint, enforces uniqueness @ times.

i have constraint , use query:

insert tbl (col1, col2, col3, col4) select 1, 2, 3, 4  not exists (    select 1 tbl    (col1, col2, col3, col4) = (1, 2, 3, 4); 

similar update.

you encapsulate insert / update in plpgsql function , trap duplicate key violations. example:

• is select or insert in function prone race conditions?