How to disable PostgreSQL triggers in one transaction only?

I had this exact same issue and figured out a clever and clean way to resolve it.

Firstly, you cannot disable a trigger within a trigger if that is the trigger that you are currently executing. That was my scenario - I was inserting into a table as a result of inserts to it - which would otherwise cause an infinite loop of triggers.

The way I resolved it was to add a local parameter variable into the mix that essentially acted as a global variable that would disable the trigger from further invocations when it was already being used.

To do so, add the following code to your trigger function, at the very beginning:

SET LOCAL your.variable_name to 'TRUE';

and then (assuming you're working with pg ≥ 9.6) you can just add the following line to your CREATE TRIGGER:

WHEN (current_setting('your.variable_name', 't') <> 'TRUE')

I haven't done any bench testing, but from my prior experiences I expect it to be very performant.

To temporarily disable all triggers in a PostgreSQL session, use this:

SET session_replication_role = replica;

That disables all triggers for the current database session only. Useful for bulk operations, but remember to be careful to keep your database consistent.

To re-enable:

SET session_replication_role = DEFAULT;

Source: http://koo.fi/blog/2013/01/08/disable-postgresql-triggers-temporarily/

You can disable all triggers in this table. It should look like this:

ALTER TABLE tblname DISABLE TRIGGER USER;
Your SQL;
ALTER TABLE tblname ENABLE TRIGGER USER;

For disabling a single trigger use this:

ALTER TABLE tblname DISABLE TRIGGER trigger_name;
Your SQL;
ALTER TABLE tblname ENABLE TRIGGER trigger_name;

You can read more about ALTER TABLE in documentation.