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

Question

I am trying to drop a column from a table. How can I check if the column exists or not?

I went through the documentation at https://www.postgresql.org/docs/9.2/static/sql-altertable.html, but didn't find any example how to do it.

Even found How to check if a column exists in a SQL Server table?, but it does not seem relevant.

The information schema query in the question you link to should work in PostgreSQL. — Peter Eisentraut
– Peter Eisentraut, Commented Nov 7, 2016 at 21:02

Edgar Orozco · Accepted Answer · 2018-08-16 05:27:45Z

178

You just need to add IF EXIST to your DROP COLUMN statement:

ALTER TABLE tableName
DROP COLUMN IF EXISTS columnName;

edited Aug 16, 2018 at 5:27

Edgar Orozco

2,77232 silver badges34 bronze badges

answered Nov 7, 2016 at 21:28

user3521432

2,8404 gold badges15 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fredrik Widerberg · Accepted Answer · 2018-08-15 21:13:01Z

6

You can also try via IF EXISTS Method which work great while we are using migration

DO $$

BEGIN

    IF EXISTS(
    SELECT column_name FROM information_schema.columns WHERE table_name = tableName AND column_name = columnName)
    THEN
        ALTER TABLE tableName DROP COLUMN columnName;
    END IF;

END $$;

edited Aug 15, 2018 at 21:13

Fredrik Widerberg

3,10810 gold badges33 silver badges43 bronze badges

answered Aug 15, 2018 at 20:52

Piyush Sharma

6618 silver badges9 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related