How to Alter Table in Postgres

-- Create index without exclusive lock
CREATE INDEX CONCURRENTLY idx_email ON users(email);

-- Then add unique constraint using the index
ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE USING INDEX idx_email;

-- Rename a table
ALTER TABLE old_table_name
RENAME TO new_table_name;

-- Add a simple column
ALTER TABLE employees
ADD COLUMN email VARCHAR(100);

-- Add a column with constraints
ALTER TABLE products
ADD COLUMN price NUMERIC(10,2) NOT NULL DEFAULT 0;

-- Add multiple columns at once
ALTER TABLE customers
ADD COLUMN phone VARCHAR(20),
ADD COLUMN fax VARCHAR(20),
ADD COLUMN preferred_contact VARCHAR(10);

-- Remove a column
ALTER TABLE employees
DROP COLUMN middle_name;

-- Remove multiple columns
ALTER TABLE legacy_data
DROP COLUMN obsolete_col1,
DROP COLUMN obsolete_col2;

-- Remove a column only if it exists
ALTER TABLE customers
DROP COLUMN IF EXISTS temporary_notes;

-- Remove a column and its dependencies
ALTER TABLE orders
DROP COLUMN status CASCADE;

-- Rename a column
ALTER TABLE products
RENAME COLUMN product_name TO name;

-- Add NOT NULL constraint
ALTER TABLE users
ALTER COLUMN email SET NOT NULL;

-- Drop NOT NULL constraint
ALTER TABLE orders
ALTER COLUMN shipping_address DROP NOT NULL;

-- Set default value
ALTER TABLE products
ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP;

-- Drop default value
ALTER TABLE employees
ALTER COLUMN status DROP DEFAULT;

-- Add primary key
ALTER TABLE products
ADD PRIMARY KEY (product_id);

-- Add unique constraint
ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);

-- Add check constraint
ALTER TABLE employees
ADD CONSTRAINT valid_salary CHECK (salary > 0);

-- Add foreign key
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers (id);

-- Add composite unique constraint
ALTER TABLE order_items
ADD CONSTRAINT unique_order_product
UNIQUE (order_id, product_id);

-- Remove constraint by name
ALTER TABLE products
DROP CONSTRAINT unique_sku;

-- Remove primary key
ALTER TABLE users
DROP CONSTRAINT users_pkey;

-- Remove constraint if exists
ALTER TABLE orders
DROP CONSTRAINT IF EXISTS fk_shipping_address;

-- Force removal of constraint and dependent objects
ALTER TABLE employees
DROP CONSTRAINT employee_department_fkey CASCADE;

-- Move table to different schema
ALTER TABLE public.events
SET SCHEMA archive;

-- Change tablespace
ALTER TABLE large_records
SET TABLESPACE fast_ssd;

-- Set fillfactor for better update performance
ALTER TABLE frequently_updated
SET (fillfactor = 70);

-- Enable/disable row security
ALTER TABLE sensitive_data
ENABLE ROW LEVEL SECURITY;

-- Add inheritance
ALTER TABLE employees_2023
INHERIT employees;

-- Remove inheritance
ALTER TABLE employees_2022
NO INHERIT employees;

-- Convert regular table to partitioned table
ALTER TABLE logs
PARTITION BY RANGE (log_date);

-- Detach a partition
ALTER TABLE logs
DETACH PARTITION logs_2022;

-- Add identity column
ALTER TABLE products
ADD COLUMN id INT GENERATED ALWAYS AS IDENTITY;

-- Modify identity column
ALTER TABLE users
ALTER COLUMN id
SET GENERATED BY DEFAULT;

-- Set identity sequence options
ALTER TABLE orders
ALTER COLUMN order_id
SET GENERATED ALWAYS AS IDENTITY
(START WITH 10000 INCREMENT BY 10);

-- Script to alter multiple tables with the same structure
DO $$
DECLARE
    t text;
BEGIN
    FOR t IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_name LIKE 'sales_%'
          AND table_schema = 'public'
    LOOP
        EXECUTE format('ALTER TABLE %I ADD COLUMN last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP', t);
    END LOOP;
END $$;

-- Find dependent views
SELECT dependent_ns.nspname as dependent_schema,
       dependent_view.relname as dependent_view
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN pg_class as source_table ON pg_depend.refobjid = source_table.oid
JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
JOIN pg_namespace source_ns ON source_ns.oid = source_table.relnamespace
WHERE source_table.relname = 'your_table'
  AND source_ns.nspname = 'your_schema'
  AND dependent_view.relkind = 'v';

-- Drop and recreate views, or use a different approach

-- Find blocking processes
SELECT pid,
       usename,
       pg_blocking_pids(pid) as blocked_by,
       query as blocked_query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- You may need to terminate blocking queries