PostgreSQL DROP COLUMN

Sometimes, removing columns became necessary. Whether you’re cleaning up your database schema, optimizing performance understanding how to use PostgreSQL’s DROP COLUMN functionality properly is essential. In this PostgreSQL tutorial, we will discuss the PostgreSQL DROP COLUMN statement, which removes a column from an existing table.

PostgreSQL DROP COLUMN

Before diving into the technical details, let’s understand why you might need to remove columns from your PostgreSQL tables:

• Database cleanup: Removing unused columns improves schema clarity
• Performance optimization: Fewer columns can mean faster query execution
• Data security: Removing columns containing sensitive information
• Application changes: When features are deprecated, their associated columns may become obsolete
• Reducing storage requirements: Removing unused columns can free up disk space

Let us deep dive into all the approaches on how to drop column in PostgreSQL.

Approach 1: Using Standard DROP COLUMN 

You can remove a column from an existing table in PostgreSQL by using the DROP COLUMN clause in the ALTER TABLE statement.

Syntax

The syntax is as follows:

ALTER TABLE table_name 
DROP COLUMN column_name;

In the above statement,

• table_name is the name of the table from which the column has to be removed.
• column_name is the name of the column that has to be removed from the table.
• The above statement instructs PostgreSQL to remove all indexes and constraints that involve the dropped column.

EXAMPLE 1: Dropping a single column

Let us create the user_data table and insert a few records into that table using the query below.

CREATE TABLE user_data (
user_id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
gender VARCHAR(10),
email VARCHAR(50) UNIQUE,
phone VARCHAR(50),
address VARCHAR(80)
);

-- Lets populate the table with some data
INSERT INTO user_data (name, gender, email, phone, address)
VALUES
('Moshe Layman', 'Female', 'mlayman0@soup.io', '4286064213', '11894 Arizona Trail'),
('Sherie Audenis', 'Male', 'saudenis1@vistaprint.com', '6783625659', '19157 Ilene Junction'),
('Odie Dearn', 'Male', 'odearn2@guardian.co.uk', '6239636719', '16920 Melrose Center'),
('Lurleen Chipman', 'Male', 'lchipman3@tuttocitta.it', '2071756699', '49123 Pierstorff Circle'),
('Letti Aim', 'Female', 'laim4@ted.com', '7571748632', '6 Larry Court'),
('Jule Willcox', 'Female', 'jwillcox5@symantec.com', '6502784678', '319 Barby Alley'),
('Lynnea Banks', 'Male', 'lbanks6@java.com', '4179248826', '29 Manufacturers Avenue'),
('Cherye MacMechan', 'Male', 'cmacmechan7@imdb.com', '6365368018', '41877 Maywood Way'),
('Noam Calderbank', 'Female', 'ncalderbank8@businessweek.com', '6607119137', '393 Crescent Oaks Crossing'),
('Dedra Chedzoy', 'Female', 'dchedzoy9@soundcloud.com', '1765939779', '07 Maryland Hill');

\d user_data

SELECT * FROM user_data;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

Now, let us use the following ALTER query to drop the column.

SELECT * FROM user_data;

ALTER TABLE user_data
DROP COLUMN address,
DROP COLUMN phone CASCADE;

SELECT * FROM user_data;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

Read PostgreSQL DATE Format with examples

Example 2: Dropping multiple columns

You can remove multiple columns from an existing table at a time in PostgreSQL by specifying multiple comma-separated DROP COLUMN clauses in an ALTER TABLE statement.

Syntax

The syntax is as follows:

ALTER TABLE table_name
DROP COLUMN column_name1,
DROP COLUMN column_name2,
...
...
...
DROP COLUMN column_nameN;

In the above syntax,

• table_name is the name of the table from which the columns have to be removed.
• column_name1, column_name2, …, column_nameN are the names of the columns that have to be removed from the table.

Example

SELECT * FROM user_data;

ALTER TABLE user_data
DROP COLUMN address,
DROP COLUMN gender,
DROP COLUMN phone;

SELECT * FROM user_data;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

This is how to drop multiple columns from a table in PostgreSQL.

Read PostgreSQL WHERE IN with examples

Approach 2: Using the IF EXISTS Clause

When you try to remove a column that does not exist in the table, PostgreSQL issues an error. You can remove a column only if it exists in PostgreSQL by using an option (keyword) IF EXISTS with the DROP COLUMN clause in the ALTER TABLE statement.

Syntax

The syntax is as follows:

ALTER TABLE table_name 
DROP COLUMN IF EXISTS column_name;

The above syntax results in the removal of the column column_name from the table if it exists, and skipping the statement if the column column_name does not exist in the table.

EXAMPLE

SELECT * FROM user_data;

ALTER TABLE user_data
DROP COLUMN phone;

ALTER TABLE user_data
DROP COLUMN IF EXISTS phone;

SELECT * FROM user_data;

After executing the above query, I got the expected output as shown in the screenshot below.

Read PostgreSQL WHERE with examples

Approach 3: Using CASCADE to Drop Dependent Objects

If there are other database objects, such as stored procedures, triggers, or views, that use the column you want to remove, you cannot remove it because other objects depend on it. You need to add an option (keyword) CASCADE to the DROP COLUMN clause, which will remove the column and all of its dependent objects.

Syntax

The syntax is as follows:

ALTER TABLE table_name 
DROP COLUMN column_name CASCADE;

Example 4: Dropping column constraint

You can drop a column constraint in PostgreSQL using the DROP CONSTRAINT clause in the ALTER TABLE statement, except for the NOT NULL constraint.

The syntax is as follows:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

In the above syntax, constraint_name is the name of the constraint, which can be found by executing the statement \d table_name, which will show the details related to the structure of the table table_name, including all the indexes, constraints, etc.

You can drop the NOT NULL constraint from a column of a table in PostgreSQL using the ALTER COLUMN DROP NOT NULL clause in the ALTER TABLE statement.

The syntax is as follows:

ALTER TABLE table_name
ALTER COLUMN column_name DROP NOT NULL;

EXAMPLE:

\d user_package

\d user_data

ALTER TABLE user_data
DROP CONSTRAINT user_data_email_key,
DROP CONSTRAINT user_data_fkey,
DROP CONSTRAINT user_data_pkey;

ALTER TABLE user_data
ALTER COLUMN name DROP NOT NULL;

\d user_data

I got the expected output after executing the above query, as shown in the screenshot below.

Read PostgreSQL CASE with examples.

Example 5: Dropping a column from view

You can remove the column(s) from a view in PostgreSQL only by recreating the VIEW as needed, because there is no direct way to drop the column(s) from a view.

PostgreSQL does not support the DROP COLUMN from a view. You need to use the DROP VIEW statement first to drop the view, and then the CREATE VIEW statement to create a new VIEW.

The syntax is as follows:

DROP VIEW view_name;

CREATE [OR REPLACE] VIEW view_name AS
SELECT column(s)
FROM table(s)
[WHERE condition(s)];

In the above syntax,

• The first statement is for dropping the View named view_name.
• And in the second statement, view_name is the name of the view to be created. column(s) are the comma-separated column names that are to be inserted in the View and the table(s) is/are the table(s) from where the columns are selected, and to be inserted in the View.

EXAMPLE:

-- Lets first create a View from the existing tables

CREATE OR REPLACE VIEW user_view AS
SELECT u.user_id, u.name, u.gender, u.email, u.phone, p.package
FROM user_data AS u
INNER JOIN user_package AS p
ON u.user_type = p.user_type;

\d

SELECT * FROM user_view;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

-- Now Lets try to drop the column(s) from the created view

SELECT * FROM user_view;

ALTER VIEW user_view
DROP COLUMN gender,
DROP COLUMN phone;

ALTER VIEW user_view
DROP COLUMN phone;

-- Both of the above statements gives error

-- PostgreSQL do not support DROP COLUMN in VIEW, Now lets try to replace the VIEW

CREATE OR REPLACE VIEW user_view AS
SELECT u.user_id, u.name, u.email, p.package
FROM user_data AS u
INNER JOIN user_package AS p
ON u.user_type = p.user_type;


-- The above Replace statement also give the error. So now, lets drop the view and recreate the view as we need

DROP VIEW user_view;

CREATE OR REPLACE VIEW user_view AS
SELECT u.user_id, u.name, u.email, p.package
FROM user_data AS u
INNER JOIN user_package AS p
ON u.user_type = p.user_type;

SELECT * FROM user_view;

After executing the above query, I got the expected output as shown in the screenshot below.

Read PostgreSQL DATE Functions with Examples

Example 6: Dropping column default value

You can remove the DEFAULT value of a column in PostgreSQL by using the DROP DEFAULT option of the ALTER COLUMN clause in the ALTER TABLE statement.

The syntax is as follows:

ALTER TABLE table_name
ALTER COLUMN column_name DROP DEFAULT;

The above syntax results in the same as if you set the DEFAULT value of the column as NULL. Additionally, there will be no error if you drop a DEFAULT value that hasn’t been defined, as the DEFAULT value is implicitly NULL in PostgreSQL.

EXAMPLE:

SELECT * FROM user_data;

\d user_data

ALTER TABLE user_data
ALTER COLUMN active DROP DEFAULT;

\d user_data

After executing the above query, I obtained the expected output, as shown in the screenshot below.

Read PostgreSQL INSERT Multiple Rows

Performance Consideration

In PostgreSQL, the DROP COLUMN clause in ALTER TABLE statements removes the column visually, but not physically, that is, it makes it invisible to SQL operations. The table sets the NULL value for the removed column after some INSERT and UPDATE operations.

It means that dropping a column is quick, but it does not reduce the physically occupied space of the table; the space is not reclaimed implicitly. It will be reclaimed over time as the existing table is updated, which reduces the performance of the operations. If the table contains a large amount of data, it can be seen through.

SO, you can use CLUSTER or REINDEX, which forces an immediate rewrite of the table in PostgreSQL. This will result in no visible change in the table, but it will eliminate unnecessary data and enhance the performance of subsequent operations.

CLUSTER table_name USING index_name;


REINDEX TABLE table_name;

In the above syntax,

• The CLUSTER statement makes the PostgreSQL cluster the table; we must specify the index name of the table to rewrite it, which has already been defined on the table name. Whenever a table is clustered, it is reordered physically based on the index information, and an ACCESS EXCLUSIVE lock is acquired on it.
• The REINDEX statement, followed by table_name, recreates all the indexes in the table table_name.

EXAMPLE:

To demonstrate the identical changes in memory space, I have populated the user_data table with 500 rows.

\dt+ user_data    -- checking the space table has taken physically

ALTER TABLE user_data
DROP COLUMN address;

\dt+ user_data

SELECT *, ctid FROM user_data ORDER BY ctid LIMIT 5;

CLUSTER user_data USING user_data_pkey;

\dt+ user_data

After executing the above query, I got the expected output as shown in the below screenshot.

\dt+ user_data

ALTER TABLE user_data
DROP COLUMN email;

\dt+ user_data

REINDEX TABLE user_data;

\dt+ user_data

CLUSTER user_data USING user_data_pkey;

\dt+ user_data

SELECT *, ctid FROM user_data ORDER BY ctid LIMIT 5;

After executing the above query, I got the expected output as shown in the below screenshot.

Conclusion

Dropping columns in PostgreSQL is essential for maintaining clean, efficient database schemas. When used properly, it can improve performance, reduce storage requirements, and make your database easier to understand and maintain.

Remember these key takeaways:

• Always back up your data before dropping columns
• Use transactions and test in development environments first
• Consider the performance implications for large tables
• Be aware of dependencies and constraints
• Choose the right approach based on your specific scenario

By following the methods and best practices I’ve explained in this article, you can drop a column efficiently.

You may also like the following PostgreSQL tutorials:

• PostgreSQL ADD COLUMN + 17 Examples
• PostgreSQL INSERT INTO table + 9 Examples
• PostgreSQL DROP TABLE
• Update query in PostgreSQL
• PostgreSQL add primary key