While creating a database table, column or index, if it already exist, postgres will throw the error and abort the whole operation. Using IF EXISTS and IF NOT EXISTS is a way to make them safe.

🔗Creating and Dropping Entire Tables

Safe Create:

CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY,
    email VARCHAR(255) NOT NULL
);

Safe Drop:

DROP TABLE IF EXISTS users;

🔗Adding and Removing Columns

Safe Add Column:

ALTER TABLE users 
ADD COLUMN IF NOT EXISTS phone_number VARCHAR(20);

Safe Drop Column:

ALTER TABLE users 
DROP COLUMN IF EXISTS phone_number;

🔗Dropping Constraints (Easy)

Safe Drop Constraint:

ALTER TABLE users 
DROP CONSTRAINT IF EXISTS unique_email_constraint;

🔗Adding Constraints (The Tricky One)

To make adding a constraint truly infallible, we have to use a tiny bit of PostgreSQL logic called a DO blocIt checks the database records first before trying to add the rule.

Safe Add Constraint (using a DO block):

DO $$ 
BEGIN
    -- Check if the constraint name already exists
    IF NOT EXISTS (
        SELECT 1 
        FROM pg_constraint 
        WHERE conname = 'unique_email_constraint'
    ) THEN
        -- If it does not exist, add it
        ALTER TABLE users 
        ADD CONSTRAINT unique_email_constraint UNIQUE (email);
    END IF;
END $$;

🔗Custom Types and Enums (Bonus)

Safe Drop Type:

DROP TYPE IF EXISTS job_status_enum;

Safe Create Type: (Postgres does not have CREATE TYPE IF NOT EXISTS, so we use another DO block)

DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'job_status_enum') THEN
        CREATE TYPE job_status_enum AS ENUM ('submitted', 'pending', 'completed');
    END IF;
END $$;