I have come accross this question many times: “If I delete a Postgres table, do I also have to delete it’s indexes, triggers and constraints before or afterwards?“. The short answer is no. When a table is dropped, Postgres will also delete it’s related indexes, triggers and constraints. In the following article a docker-compose.yml file and some code snippets are provided if you want to validate this behaviour.

The code snippets related to this article can also be found here.

If you do not want to clone the Git reposity, first create a new empty working directory. Inside this directory create a new docker-compose.yml file:

# file docker-compose.yml
version: '3.3'
services:

    postgres:
        # Name of the superuser will be "postgres" if not specified otherwise. 
        # The default database will be named after the superuser (e.g. "postgres").

        # $ psql -h localhost -p 5432 -U postgre

        # https://hub.docker.com/_/postgres
        image: postgres:14.4
        environment: 
            POSTGRES_PASSWORD: postgrespwd
        
        ports:
            - 5432:5432

Then execute $ docker-compose up --build to build the image and start the container. To connect to the database use the following connection details:

• host: localhost
• port: 5432
• database: postgres
• username: postgres
• password: postgrespwd

Once you are connected (e.g. using DBeaver) you can test the behaviour when deleting tables in a PostgreSQL database using the SQL statements in the following sections.

By the way, if you want to delete all docker related resources (containers, images, volumes, …) you can do this by executing $ docker system prune --all.

Creating the trigger function

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.updated_at = now(); 
   RETURN NEW;
END;
$$ language 'plpgsql';

Creating the table and associated resources (indexes, …)

CREATE TABLE users
(
    id          UUID PRIMARY KEY,
    nickname    TEXT NOT NULL,
    description TEXT, 
    updated_at  TIMESTAMP WITHOUT TIME ZONE DEFAULT (now() AT TIME ZONE 'utc'),
    CONSTRAINT  users_nickname_unique UNIQUE (nickname)
);

CREATE INDEX users_description_idx ON users (description);

CREATE TRIGGER users_updated_at_trigger
    BEFORE UPDATE
    ON users
    FOR EACH ROW
EXECUTE PROCEDURE update_updated_at_column();

Deleting the table

DROP TABLE users;

Validating the result

List relevant indexes

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

List relevant triggers

SELECT event_object_table AS table_name ,trigger_name         
FROM information_schema.triggers  
GROUP BY table_name , trigger_name 
ORDER BY table_name ,trigger_name 

List relevant constraints

SELECT * from pg_catalog.pg_constraint
WHERE conname like 'users_%';

The code snippets related to this article can also be found here.