PostgreSQL Tutorial

• PostgreSQL - Introduction
• Install PostgreSQL on Windows
• Install PostgreSQL on Mac
• PostgreSQL - Loading a Database
• PostgreSQL - Create Database
• PostgreSQL - ALTER DATABASE
• PostgreSQL - DROP DATABASE
• PostgreSQL - Rename Database
• PostgreSQL - Copy Database
• PostgreSQL - Size of a Database
• PostgreSQL - Show Databases

Data Types

• PostgreSQL - Data Types
• PostgreSQL - Boolean Data Type
• PostgreSQL - CHAR Data Type
• PostgreSQL - VARCHAR Data Type
• PostgreSQL - TEXT Data Type
• PostgreSQL - NUMERIC Data Type
• PostgreSQL - SMALLINT Integer Data Type
• PostgreSQL - INTEGER Data Type
• PostgreSQL - BIGINT Integer Data Type
• PostgreSQL - Date Data Type
• PostgreSQL - Timestamp Data Type
• PostgreSQL - UUID Data Type
• PostgreSQL - TIME Data Type
• PostgreSQL - Interval Data Type
• PostgreSQL - User-defined Data Type
• PostgreSQL - Array Data Type
• PostgreSQL - hstore Data Type
• PostgreSQL - JSON Data Type

Querying & Filtering Data

• PostgreSQL - SELECT
• PostgreSQL - SELECT DISTINCT clause
• PostgreSQL - ORDER BY clause
• PostgreSQL - WHERE clause
• PostgreSQL - LIMIT clause
• PostgreSQL - FETCH clause
• PostgreSQL - IN operator
• PostgreSQL - IS NULL operator
• PostgreSQL - LIKE operator
• PostgreSQL - NOT LIKE operator
• PostgreSQL - BETWEEN operator
• PostgreSQL - HAVING clause
• PostgreSQL - GROUP BY clause

Managing Tables

• PostgreSQL - CREATE TABLE
• PostgreSQL - SELECT INTO
• PostgreSQL - Create Auto-increment Column using SERIAL
• PostgreSQL - CREATE SEQUENCE
• PostgreSQL - Identity Column
• PostgreSQL - ALTER TABLE
• PostgreSQL - Rename Table
• PostgreSQL - ADD COLUMN
• PostgreSQL - DROP COLUMN
• PostgreSQL - Change Column Type
• PostgreSQL - RENAME COLUMN
• PostgreSQL - DROP TABLE
• PostgreSQL - TRUNCATE TABLE
• PostgreSQL - Temporary Table
• PostgreSQL - Temporary table name
• PostgreSQL - Removing Temporary Table
• PostgreSQL - Copy a Table
• PostgreSQL - Comparing Tables
• PostgreSQL - Describe Table
• PostgreSQL - Show Tables
• PostgreSQL - Import CSV File Into Table

Modifying Data

• PostgreSQL - INSERT
• PostgreSQL - Insert multiple rows
• PostgreSQL - UPDATE
• PostgreSQL - DELETE
• PostgreSQL - Upsert

Conditionals

• PostgreSQL - CASE
• PostgreSQL - COALESCE
• PostgreSQL - NULLIF() Function
• PostgreSQL - CAST

Control Flow

• PostgreSQL - IF Statement
• PostgreSQL - CASE Statement
• PostgreSQL - Loop Statement
• PostgreSQL - While Loops
• PostgreSQL - For Loops
• PostgreSQL - Exit
• PostgreSQL - Continue

Transactions & Constraints

• PostgreSQL - Transactions
• PostgreSQL - COMMIT
• PostgreSQL - BEGIN
• PostgreSQL - ROLLBACK
• PostgreSQL - Primary Key
• PostgreSQL - Foreign Key
• PostgreSQL - CHECK Constraint
• PostgreSQL - UNIQUE Constraint
• PostgreSQL - NOT NULL Constraint

Working with JOINS & Schemas

• PostgreSQL - Joins
• PostgreSQL - LEFT JOIN
• PostgreSQL - INNER JOIN
• PostgreSQL - FULL OUTER JOIN
• PostgreSQL - SELF JOIN
• PostgreSQL - Schema
• PostgreSQL - CREATE SCHEMA
• PostgreSQL - DROP SCHEMA
• PostgreSQL - ALTER SCHEMA

Roles & Permissions

• PostgreSQL - CREATE ROLE
• PostgreSQL - ALTER ROLE
• PostgreSQL - DROP ROLE
• PostgreSQL - GRANT
• PostgreSQL - REVOKE
• PostgreSQL - Role Membership

Working with Sets

• PostgreSQL - UNION operator
• PostgreSQL - INTERSECT Operator
• PostgreSQL - EXCEPT Operator
• PostgreSQL - GROUPING SETS
• PostgreSQL - CUBE
• PostgreSQL - ROLLUP

Subquery & CTEs

• PostgreSQL - ANY Operator
• PostgreSQL - ALL Operator
• PostgreSQL - EXISTS Operator
• PostgreSQL - CTE
• PostgreSQL - Deleting Duplicate Rows using Subquery

User-defined Functions

• PostgreSQL - CREATE FUNCTION Statement
• PostgreSQL - Function parameter modes
• PostgreSQL - Function Overloading
• PostgreSQL - Function that returns table
• PostgreSQL - Drop Function

Important In-Built Functions

• PostgreSQL - AVG() Function
• PostgreSQL - COUNT() Function
• PostgreSQL - MAX() Function
• PostgreSQL - MIN() Function
• PostgreSQL - SUM() Function
• PostgreSQL - FIRST_VALUE Function
• PostgreSQL - LAST_VALUE Function
• PostgreSQL - NTH_VALUE Function
• PostgreSQL - ROW_NUMBER Function
• PostgreSQL - CURRENT_DATE Function
• PostgreSQL - CURRENT_TIME Function
• PostgreSQL - EXTRACT Function
• PostgreSQL- CONCAT Function
• PostgreSQL - FORMAT Function
• PostgreSQL - UPPER function
• PostgreSQL- LOWER function
• PostgreSQL - REGEXP_MATCHES Function
• PostgreSQL - REGEXP_REPLACE Function
• PostgreSQL - REPLACE Function

PostgreSQL PL/pgSQL

• PostgreSQL - Dollar-Quoted String Constants
• PostgreSQL - Block Structure

Variables & Constants

• PostgreSQL - Variables
• PostgreSQL - Select Into
• PostgreSQL - Row type variables
• PostgreSQL - Record type variable
• PostgreSQL - Constants

Stored Procedures

• PostgreSQL - Introduction to Stored Procedures
• PostgreSQL - CREATE PROCEDURE
• PostgreSQL - Drop Procedure

Working with Triggers

• PostgreSQL - Trigger
• PostgreSQL - CREATE TRIGGER
• PostgreSQL - DROP TRIGGER
• PostgreSQL - ALTER TRIGGER
• PostgreSQL - Disabling a Trigger
• PostgreSQL - Enabling a Trigger

Working with Views & Indexes

• PostgreSQL - CREATE INDEX
• PostgreSQL - DROP INDEX
• PostgreSQL - List Indexes
• PostgreSQL - UNIQUE Index
• PostgreSQL - Partial Index
• PostgreSQL - Index On Expression
• PostgreSQL - REINDEX
• PostgreSQL - Multicolumn Indexes

Errors & Exception Handling

• PostgreSQL - Errors and Messages
• PostgreSQL - Exception Handling
• PostgreSQL - Assert

PostgreSQL - Enabling a Trigger

In PostgreSQL, after creating a trigger, it is enabled by default. However, there might be scenarios where a trigger is disabled (perhaps for maintenance or troubleshooting), and you'd want to re-enable it. To enable a trigger, you use the ALTER TABLE command along with the ENABLE TRIGGER clause.

Basic Syntax:

ALTER TABLE table_name ENABLE TRIGGER trigger_name;

• table_name: The name of the table associated with the trigger.

• trigger_name: The name of the trigger you want to enable.

Examples:

1. Enable a Single Trigger:

   If you have a trigger named trg_before_insert on the students table and you want to enable it:

   ALTER TABLE students ENABLE TRIGGER trg_before_insert;
   

2. Enable All Triggers on a Table:

   If you want to enable all triggers associated with the students table:

   ALTER TABLE students ENABLE ALL TRIGGERS;
   

Additional Information:

• To disable a trigger, you'd use the DISABLE TRIGGER clause instead of ENABLE TRIGGER.

• If you're performing bulk operations or other maintenance tasks, you might temporarily disable a trigger to speed things up or prevent unwanted side effects. However, always remember to re-enable the trigger afterward to ensure your database remains consistent and all necessary actions are taken on the specified events.

• Only the table owner or a superuser can enable or disable a trigger.

When enabling (or disabling) triggers, always ensure that you understand the implications and effects on your application's behavior and data integrity. Triggers can be fundamental to maintaining database consistency and executing necessary operations, so it's essential to handle them with care.

1. Enabling a disabled trigger in PostgreSQL:

   • Description: If a trigger is disabled, you can enable it using the ENABLE TRIGGER statement.
   • Code:

     ENABLE TRIGGER trigger_name ON table_name;
     

2. Activating triggers on specific tables in PostgreSQL:

   • Description: Activate triggers on specific tables by specifying the table name in the ENABLE TRIGGER statement.
   • Code:

     ENABLE TRIGGER trigger_name ON specific_table;
     

3. Enabling multiple triggers at once in PostgreSQL:

   • Description: To enable multiple triggers simultaneously, list them in the ENABLE TRIGGER statement.
   • Code:

     ENABLE TRIGGER trigger_name1, trigger_name2 ON table_name;
     

4. Using ALTER TABLE to enable triggers in PostgreSQL:

   • Description: The ALTER TABLE statement can be used to enable or disable all triggers on a table.
   • Code:

     ALTER TABLE table_name ENABLE TRIGGER ALL;
     

5. Enabling triggers with specific conditions in PostgreSQL:

   • Description: Specify conditions using the WHEN clause when enabling triggers to control when they activate.
   • Code:

     ENABLE TRIGGER trigger_name ON table_name WHEN (condition);
     

6. Enabling triggers on specific events in PostgreSQL:

   • Description: Enable triggers on specific events (e.g., BEFORE INSERT, AFTER UPDATE) using the appropriate clause.
   • Code:

     ENABLE TRIGGER trigger_name ON table_name BEFORE INSERT;
     

7. Re-enabling triggers after maintenance in PostgreSQL:

   • Description: After maintenance activities, you might need to re-enable triggers using the ENABLE TRIGGER statement.
   • Code:

     ENABLE TRIGGER ALL;