patternsqlModerate

Can a PostgreSQL Trigger be used to ignore an insert?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

postgresqlignorecantriggerinsertused

Problem

I've got a situation where there are dozens of various triggers in our database that trigger a record being inserted into another (single) table within the database.

I was hoping that while testing our replacement server, that there was some easy way that I could set up a trigger on that single table to effectively ignore all of the other triggers. (rather than haing to remove dozens of triggers and then re-enable them when we go into production).

I was hoping to be able to use an INSTEAD OF INSERT trigger set to a function that did nothing, but you can only use those on views, not tables. I tried a BEFORE INSERT trigger that called RAISE EXCEPTION, but that results in the whole transaction failing, so the modifications to the other tables get rejected as well.

Is there some easy way to ignore inserts to that table, or should I just do an AFTER INSERT that clears the record out afterwards?

Solution

Yes, use a BEFORE INSERT trigger, and return null.

Live example here.

create table stuff (
  stuff_id int primary key,
  thing text
);

create or replace function stuff_inserting() returns trigger language plpgsql as $
begin

  return null;

end $;

insert into stuff values (1, 'asdf');

select * from stuff; /* returns 1 row */

create trigger inserting before insert on stuff for each row execute procedure stuff_inserting();

insert into stuff values (2, 'fdsa');

select * from stuff; /* still returns only 1 row */

Code Snippets

create table stuff (
  stuff_id int primary key,
  thing text
);

create or replace function stuff_inserting() returns trigger language plpgsql as $$
begin

  return null;

end $$;

insert into stuff values (1, 'asdf');

select * from stuff; /* returns 1 row */

create trigger inserting before insert on stuff for each row execute procedure stuff_inserting();

insert into stuff values (2, 'fdsa');

select * from stuff; /* still returns only 1 row */

Context

StackExchange Database Administrators Q#76333, answer score: 15

Revisions (0)

No revisions yet.