This quick tutorial will show you how to delete/destroy/drop a table column in a PostgreSQL database.
Before you Delete a Table in your Database…
There are a few things you should do before you try and delete a table from your PostgreSQL database, especially if you’re working with data in production.
First, take a backup of your PostgreSQL server – just in case you delete the wrong thing, or change your mind later.
Next, make sure you’re logged in as the default postgresql admin user, or a user with permission to delete the table in question.
And finally, make sure you’re sure of the name of the column (and the table it’s in) that you want to delete, and that there’s nothing you need to keep in it, and that no other objects in your database are relying on it. Deleting cannot be undone (unless you have a backup to restore).
Deleting a Column from a PostgreSQL Table Using DROP COLUMN
To delete a table column in a PostgreSQL database you will need to use the ALTER TABLE and DROP COLUMN statements:
ALTER TABLE table_name DROP COLUMN column_name;
Multiple columns can be deleted at the same time by listing them, separated by a comma:
ALTER TABLE table_name DROP COLUMN column_1_, column_2;
Deleting a Column Only if it Exists
An error will be returned if you try to delete a column that does not exist. This can be avoided by checking whether the column exists before deleting it:
ALTER TABLE table_name DROP COLUMN IF EXISTS column_name;
Deleting a Column and All Associated Objects
If you try to delete a column that is referenced by other objects in the database, an error will be received. To delete a column and all objects that are associated with it, add the CASCADE statement:
ALTER TABLE table_name DROP COLUMN column_name CASCADE;
Be careful when doing this, and make sure you’re aware of exactly what objects rely on the column being deleted, as they will be removed, as will other reliant entities and any data they contain.
Safely Deleting Columns with RESTRICT
To remove a column only if there are no foreign keys or other objects in PostgreSQL that rely on it, you can use the RESTRICT statement:
ALTER TABLE table_name DROP COLUMN column_name RESTRICT;
If the check fails, the column deletion will fail. Note that this is now the default behaviour in PostgreSQL.
Remember, when a table column is removed, so is all of the data in it!
