PostgreSQL DROP COLUMN + 7 examples

In this PostgreSQL tutorial, we will discuss, the PostgreSQL DROP COLUMN statement to remove a column from an existing table and will cover the following topic:

• PostgreSQL DROP COLUMN
• PostgreSQL drop multiple columns
• PostgreSQL DROP COLUMN IF EXISTS
• PostgreSQL DROP COLUMN constraint
• PostgreSQL DROP COLUMN from view
• PostgreSQL DROP COLUMN default value
• PostgreSQL DROP COLUMN performance

PostgreSQL DROP COLUMN

You can remove a column from an existing table in PostgreSQL by using the DROP COLUMN clause in ALTER TABLE statement. 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 where 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 makes PostgreSQL, remove all the indexes and constraints that involved the dropped column.

NOTE – If there are other database objects like stored procedures, triggers, views, etc., where the column that you want to remove is used, you cannot remove that column as other objects are depending 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. The syntax is as follows:

ALTER TABLE table_name 
DROP COLUMN column_name CASCADE;

EXAMPLE:

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;

SELECT * FROM user_data;

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

SELECT * FROM user_data;

This is how to drop column from a table in PostgreSQL.

Read PostgreSQL DATE Format with examples

PostgreSQL drop multiple columns

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

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 where the columns has to be removed.
• column_name1, column_name2, …, column_nameN, are the name of the columns, that has 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;

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

Read PostgreSQL WHERE IN with examples

PostgreSQL DROP COLUMN IF EXISTS

When you try to remove a column that does not exist in the table, then 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. 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 existed, and skipping the statement if the column column_name did 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;

This is how to drop a column if exists in PostgreSQL.

Read PostgreSQL WHERE with examples

PostgreSQL DROP COLUMN constraint

You can drop a column constraint in PostgreSQL using the DROP CONSTRAINT clause in ALTER TABLE statement, except for the NOT NULL constraint. The syntax is as follow:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

In the above syntax, constraint_name is the name of the constraint, that 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., details.

You can drop the NOT NULL constraint from a column of a table in PostgreSQL using the ALTER COLUMN DROP NOT NULL clause in ALTER TABLE statement. The syntax is as follow:

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

Read PostgreSQL CASE with examples.

PostgreSQL DROP COLUMN from view

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

PostgreSQL does not support DROP COLUMN from view. You need to use the DROP VIEW statement first, to drop the view, and then 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;

-- 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;

This is how to drop a column for a view in PostgreSQL.

Read PostgreSQL DATE Functions with Examples

PostgreSQL DROP 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 as the same as if you set the DEFAULT value of the column as NULL. Also, there will not be any error if you drop a DEFAULT value that hadn’t been defined, as implicitly the DEFAULT value is NULL in PostgreSQL.

EXAMPLE:

SELECT * FROM user_data;

\d user_data

ALTER TABLE user_data
ALTER COLUMN active DROP DEFAULT;

\d user_data

Read PostgreSQL INSERT Multiple Rows

PostgreSQL DROP COLUMN performance

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

It means, 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 consists of huge data, then it can be seen through.

SO, you can use CLUSTER, or REINDEX that forces an immediate rewrite of the table in PostgreSQL. This will result in not a visible change in the table, but it will get rid of the unuseful data and improves the performance of further operations.

CLUSTER table_name USING index_name;


REINDEX TABLE table_name;

In the above syntax,

• CLUSTER statement makes the PostgreSQL cluster the table, we have to specify any index index_name of the table to rewrite the table, that has already been defined on the table 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 show the identical changes in the memory space, I have populated the table user_data 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

\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;

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

In this PostgreSQL tutorial, we have learned about the PostgreSQL DROP COLUMN statement to remove a column from an existing table and have covered the following topic:

• PostgreSQL DROP COLUMN
• PostgreSQL drop multiple columns
• PostgreSQL DROP COLUMN IF EXISTS
• PostgreSQL DROP COLUMN constraint
• PostgreSQL DROP COLUMN from view
• PostgreSQL DROP COLUMN default value
• PostgreSQL DROP COLUMN performance