How to drop column if it exists in PostgreSQL 9+?

Why you need IF EXISTS

In live databases, attempting to drop a column that doesn't exist can trigger errors, destabilizing your environment. Including IF EXISTS prevents these risks, ensuring smooth and error-free execution. It's the safety harness of PostgreSQL - not always needed, but you're glad it's there when you do!

Confirm column existence with SQL

You might want to verify a column's existence before dropping it. For that, you can use a PL/pgSQL block, which check first, then drops the column if it exists:

DO $$
BEGIN
   IF EXISTS (
       SELECT FROM information_schema.columns 
       WHERE  table_name  = 'your_table' 
       AND column_name  = 'your_column'
   )  THEN  
       --This is like database spring cleaning. If it doesn't spark joy, drop it!
       ALTER TABLE your_table DROP COLUMN your_column; 
   END IF;
END
$$;


Importance of script structure

Better script readability and maintenance are achieved by:

1. Transactions: operation is an all-or-nothing scenario.
2. Comments: enlighten your fellow developers.
3. SQL formatting: indent control structures for easier reading.

Like a well-organized toolkit, each component has its place and clear labeling.

Handle dependencies effectively

Columns might be referenced by views or foreign keys. Prior to dropping the column, ensure to:

• Check for dependent objects.
• Update or drop such objects proactively.
• Consider using the CASCADE option to auto-remove all dependencies:

ALTER TABLE table_name 
DROP COLUMN IF EXISTS column_name CASCADE; 


Extend your knowledge

Specify the schema

Ensure to specify the schema if the table isn't in the default public schema:

ALTER TABLE schema_name.table_name 
DROP COLUMN IF EXISTS column_name;


Automation with functions

Create a function to drop a column by passing the table name and column name parameters:

CREATE OR REPLACE FUNCTION drop_column_if_exists(table_name TEXT, column_name TEXT)
RETURNS void AS
$$
BEGIN
   --It's like a magician's trick: Now you see it, now you don't!
   EXECUTE format('ALTER TABLE %I DROP COLUMN IF EXISTS %I', table_name, column_name);
END;
$$
LANGUAGE plpgsql;


This makes dropping columns as easy as typing drop_column_if_exists('table', 'column').

Version Control Best Practices

For version control:

• Document the rationale for changes.
• Provide a rollback mechanism for emergencies.