CODEWITHSUNDEEP
sqlpostgresqlsql-insertdatabase-trigger
MMacphail
MMacphail

Reputation: 561

Can you prevent an insert in an after trigger in pgsql?

In postgres, I have an after-insert trigger on a partitionned table that checks if the record is unique or not. Currently, if the record is not unique, the trigger raises an exception. This is not what I want because I just want to ignore this case and act as if the insertion actually happened.

Is it possible to configure the trigger to silently fail ? The surrounding transaction should not fail, but the row should not be inserted.

Upvotes: 4

Views: 281

Answers (1)

GMB
GMB

Reputation: 222432

I have an after-insert trigger on a partitionned table that checks if the record is unique or not.

You could do this as part of your insert statement, using the on conflict do nothing clause. This is simpler and more efficient than using a trigger.

For this to work, you need a unique constraint on the column (or the tuple of columns) whose uniqueness you want to enforce. Assuming that this is column id, you would do:

insert into mytable(id, ...)    -- enumerate the target columns here
values(id, ...)                 -- enumerate the values to insert here
on conflict(id) do nothing

Note that conflict action do nothing does not actually requires specifiying a conflict target, so strictly speaking you could just write this as on conflict do nothing instead. I find that it is always a good idea to specify the conflict target, so the scope is better defined.

If for some reason, you cannot have a unique index on the target column, then another option is to use the insert ... select syntax along with a not exists condition:

insert into mytable(id, ...)
select id, ...
from (values(id, ...)) t(id, ...)
where not exists (select 1 from mytable t1 where t1.id = t.id)

Upvotes: 2

Related Questions