How to Disable Triggers in PostgreSQL

PostgreSQL Triggers are certain database callback functions that are executed automatically when certain events occur. But sometimes you may need to disable triggers in PostgreSQL to perform certain operations and reduce query run time. In this article, we will learn how to disable triggers in PostgreSQL.

How to Disable Triggers in PostgreSQL

Some PostgreSQL triggers are configured to run before the SQL operation such as INSERT, UPDATE, DELETE, while some are configured to after the operation. Some are even configured to run instead of SQL operations.

Some triggers are performed on a row-by-row basis, that is, executed for each row that is added/modified/deleted, while some are triggered once before/after the operation, irrespective of how many rows are modified.

Generally, many triggers are called while inserting or updating tables, on a row-by-row basis which can increase the query execution time. In order to avoid this problem, many developers disable triggers before operation, and re-enable it afterwards.

You can easily disable trigger on a specific table using following command.

ALTER TABLE table_name DISABLE TRIGGER trigger_name

For example, if you want to disable USER trigger on table DATA, then here is the query for it.

ALTER TABLE DATA DISABLE TRIGGER USER

On the other hand, if you want to re-enable trigger, you can run the following command.

ALTER TABLE table_name ENABLE TRIGGER trigger_name

Here is the command to re-enable USER trigger on DATA table.

ALTER TABLE DATA ENABLE TRIGGER USER

In this article, we have learnt how to disable and re-enable database triggers in PostgreSQL.

Also read:

Convert Text File to Excel Using Shell Script
How to Move Directory to Another Git Repository
How to Move Directory to Another Partition in Linux
How to Search PDF File in Linux
How to Schedule Task in Python