In this PostgreSQL tutorial, we will discuss, about PostgreSQL ALTER TABLE statement to modify the structure of an existing table and will cover the following topic:
- PostgreSQL ALTER TABLE
- PostgreSQL ALTER TABLE ADD COLUMN
- PostgreSQL ALTER TABLE RENAME COLUMN
- PostgreSQL ALTER TABLE ALTER COLUMN
- PostgreSQL ALTER TABLE modify column type
- PostgreSQL ALTER TABLE modify column size
- PostgreSQL ALTER TABLE DROP COLUMN
- PostgreSQL ALTER TABLE ADD CONSTRAINT
- PostgreSQL ALTER TABLE DROP CONSTRAINT
- PostgreSQL ALTER TABLE ADD FOREIGN KEY
- PostgreSQL ALTER TABLE ADD PRIMARY KEY
- PostgreSQL ALTER TABLE DROP PRIMARY KEY
- PostgreSQL ALTER TABLE rename PRIMARY KEY
- PostgreSQL ALTER TABLE change PRIMARY KEY
- PostgreSQL ALTER TABLE ADD PRIMARY KEY with name
- PostgreSQL ALTER TABLE ADD PRIMARY KEY IF NOT EXIST
- PostgreSQL ALTER TABLE ADD PRIMARY KEY autoincrement
- PostgreSQL ALTER TABLE ADD PRIMARY KEY using index
- PostgreSQL ALTER TABLE ADD PRIMARY KEY tablespace
- PostgreSQL ALTER TABLE name
PostgreSQL ALTER TABLE
You can change the structure of an existing table in PostgreSQL by using ALTER TABLE statement, The Syntax for the basic ALTER TABLE statement is as follows:
ALTER TABLE table_name
action;
In the above syntax,
- table_name is the name of the table to be modified.
- action can be any alteration supported by PostgreSQL.
The action can be any of the following:
- Add a column
- Rename a column
- Change the data type of a column
- Set a default value for the column
- Drop a column
- Add a constraint to a column
- Rename a table
All the actions will be covered in the below topics.
Let’s create a table in PostgreSQL for the examples and populate it with some data:
CREATE TABLE student_data (
first_name VARCHAR(50),
last_name VARCHAR(50),
gender VARCHAR(50),
class INT,
phone VARCHAR(50)
);
INSERT INTO student_data (first_name, last_name, gender, class, phone) VALUES ('Dasie', 'Yoodall', 'Female', 7, '7157328825');
INSERT INTO student_data (first_name, last_name, gender, class, phone) VALUES ('Valeda', 'Toulmin', 'Female', 11, '4291088592');
INSERT INTO student_data (first_name, last_name, gender, class, phone) VALUES ('Almira', 'Roke', 'Male', 8, '3555637379');
INSERT INTO student_data (first_name, last_name, gender, class, phone) VALUES ('Kerwin', 'Carcass', 'Female', 10, '3694724692');
INSERT INTO student_data (first_name, last_name, gender, class, phone) VALUES ('Dorian', 'Heindrich', 'Female', 7, '1186875862');
INSERT INTO student_data (first_name, last_name, gender, class, phone) VALUES ('Chandler', 'Bolliver', 'Female', 12, '9618136596');
INSERT INTO student_data (first_name, last_name, gender, class, phone) VALUES ('Lucille', 'Hutfield', 'Female', 12, '9628602674');
INSERT INTO student_data (first_name, last_name, gender, class, phone) VALUES ('Anson', 'Rosenblatt', 'Female', 8, '3198647424');
INSERT INTO student_data (first_name, last_name, gender, class, phone) VALUES ('Ransom', 'Smales', 'Male', 11, '5644871781');
INSERT INTO student_data (first_name, last_name, gender, class, phone) VALUES ('Ira', 'McVie', 'Female', 11, '8621483423');

SELECT * FROM student_data;

Read PostgreSQL Export Table to CSV
PostgreSQL ALTER TABLE ADD COLUMN
To add a new column to an existing table in PostgreSQL, you can use the ALTER TABLE ADD COLUMN statement. The syntax is as follow:
ALTER TABLE table_name
ADD COLUMN column_name datatype column_constraint;
In the above syntax,
- column_name is the name of the column to be added in the table table_name.
- datatype is any data type supported by the PostgreSQL specified for the newly added column.
- column_constraint specifies the constraint to the column.
Example:
\d student_data
ALTER TABLE student_data
ADD COLUMN Dayscholar BOOLEAN DEFAULT FALSE;
\d student_data
SELECT * FROM student_data;

This is how to add a column by using PostgreSQL ALTER TABLE.
Read How to connect to PostgreSQL database
PostgreSQL ALTER TABLE RENAME COLUMN
To rename a column in PostgreSQL, you can use the ALTER TABLE RENAME COLUMN TO statement. The syntax is as follow:
ALTER TABLE table_name
RENAME COLUMN column_name
TO new_column_name;
Example:
\d student_data
ALTER TABLE student_data
RENAME COLUMN dayscholar
TO hosteller;
\d student_data

This is how to rename a column using the PostgreSQL Alter table.
Read PostgreSQL CREATE INDEX + 13 Useful Examples
PostgreSQL ALTER TABLE ALTER COLUMN
You can modify a column in a table in PostgreSQL by using the ALTER TABLE ALTER COLUMN statement. The syntax is as follow:
ALTER TABLE table_name
ALTER COLUMN column_name1 action;
In the above syntax, action can be any modification that you want in the column.
You can modify the column in the following ways:
- ALTER COLUMN type
- ALTER COLUMN size
- ALTER COLUMN default value: To change a default value of the column
ALTER TABLE table_name
ALTER COLUMN column_name [SET DEFAULT value | DROP DEFAULT];
- ALTER COLUMN not null: To change the NOT NULL constraint.
ALTER TABLE table_name
ALTER COLUMN column_name [SET NOT NULL | DROP NOT NULL];
Example:
\d student_data
ALTER TABLE student_data
ALTER COLUMN hosteller DROP DEFAULT;
\d student_data
ALTER TABLE student_data
ALTER COLUMN hosteller SET DEFAULT true;
\d student_data

\d student_data
ALTER TABLE student_data
ALTER COLUMN first_name SET NOT NULL,
ALTER COLUMN last_name SET NOT NULL;
\d student_data
ALTER TABLE student_data
ALTER COLUMN last_name DROP NOT NULL;
\d student_data

Read PostgreSQL DATE Functions with Examples
PostgreSQL ALTER TABLE modify column type
You can change the data type of a column in an existing in PostgreSQL by using ALTER TABLE ALTER COLUMN [SET DATA] TYPE statement. The syntax is as follow:
ALTER TABLE table_name
ALTER COLUMN column_name [SET DATA] TYPE new_data_type;
In the above syntax, the SET DATA TYPE and TYPE are equivalent, you can use either of them and provide a new data type supported by PostgreSQL after the TYPE clause.
Example:
\d student_data
ALTER TABLE student_data
ALTER COLUMN class TYPE VARCHAR(20);
\d student_data

You can also convert the values of a column to the new ones while changing its data type by adding a USING clause as follows:
ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_data_type
USING expression;
Example:
\d student_data
ALTER TABLE student_data
ALTER COLUMN class TYPE INT; -- It will throw an Error, and give a great Hint
ALTER TABLE student_data
ALTER COLUMN class TYPE INT
USING class::integer; -- It will work
\d student_data

This is how to modify column type by using ALTER TABLE in PostgreSQL.
PostgreSQL ALTER TABLE modify column size
You can change the size of the column of a table in PostgreSQL by specifying the datatype and new size in the ALTER TABLE ALTER COLUMN TYPE statement. The syntax is as follow:
ALTER TABLE table_name
ALTER COLUMN column_name TYPE data_type(new_size);
NOTE – In the above syntax, the new_size value must be greater than the size of the values in the column.
Example:
\d student_data
ALTER TABLE student_data
ALTER COLUMN gender TYPE VARCHAR(10);
\d student_data
ALTER TABLE student_data
ALTER COLUMN gender TYPE VARCHAR(5);
\d student_data

This is how to modify column size in PostgreSQL using Alter table.
PostgreSQL ALTER TABLE DROP COLUMN
You can remove a column from a table in PostgreSQL by using ALTER TABLE DROP COLUMN statement. The syntax is as follow:
ALTER TABLE table_name
DROP COLUMN column_name;
Example:
\d student_data
ALTER TABLE student_data
DROP COLUMN hosteller;
\d student_data

This is an example of PostgreSQL ALTER TABLE DROP COLUMN.
Read PostgreSQL DATE Format with examples
PostgreSQL ALTER TABLE ADD CONSTRAINT
You can add a constraint to a table by using the ALTER TABLE ADD CONSTRAINT statement. The syntax is as follow:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_definition;
In the above syntax, constraint_name specifies the name of the newly created constraint, followed by the definition of the constraint. A constraint can be UNIQUE, PRIMARY KEY, FOREIGN KEY, etc.
Example:
\d student_data
ALTER TABLE student_data
ADD CONSTRAINT unique_con UNIQUE(first_name);
\d student_data

This is how to add constraints using Alter table in PostgreSQL.
PostgreSQL ALTER TABLE DROP CONSTRAINT
You can remove a constraint in PostgreSQL by using the ALTER TABLE DROP CONSTRAINT statement. But you must know the name of the constraint to do so. If you don’t, then you can check it by executing the statement \d table_name.
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
Example:
\d student_data
ALTER TABLE student_data
DROP CONSTRAINT unique_con;
\d student_data

This is how to drop constraint using Alter table in PostgreSQL.
Read PostgreSQL WHERE IN with examples
PostgreSQL ALTER TABLE ADD FOREIGN KEY
You can add FOREIGN KEY to the table in PostgreSQL, by using the ALTER TABLE ADD FOREIGN KEY statement. The syntax is as follow:
ALTER TABLE table_name1
ADD FOREIGN KEY (column_name1) REFERENCES table_name2
In the above syntax,
- table_name1 is the name of the table where you want to add a FOREIGN KEY
- column_name1 is the name of the column that you want make the FOREIGN KEY of the table_name1.
- table_name2 is the name of the table whose primary key column you want to reference (make foreign key column) in the table_name1.
Example:
\d class_data
\d student_data
ALTER TABLE student_data
ADD FOREIGN KEY (class) REFERENCES class_data;
\d student_data

This is how to add a foreign key using PostgreSQL ALTER TABLE.
PostgreSQL ALTER TABLE ADD PRIMARY KEY
You can add a PRIMARY KEY to an existing table in PostgreSQL by using the ALTER TABLE ADD PRIMARY KEY statement. The syntax is as follow:
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);
In the above syntax, column_name is the name of the candidate key(s) (column with UNIQUE and NOT NULL values), that can become PRIMARY KEY. If there is no candidate key(s), in the table, you can add a column with a PRIMARY KEY constraint with autoincrement values, which is done in the later topic below.
Example:
\d student_data
ALTER TABLE student_data
ADD PRIMARY KEY (phone);
\d student_data

This is how to add a primary key using ALTER TABLE in PostgreSQL.
Read PostgreSQL CASE with Examples
PostgreSQL ALTER TABLE DROP PRIMARY KEY
You can remove a PRIMARY KEY by using ALTER TABLE DROP CONSTRAINT statement and specifying the name of the PRIMARY KEY constraint. The syntax is as follow:
ALTER TABLE table_name
DROP CONSTRAINT primary_key_constraint_name;
Example:
\d student_data
ALTER TABLE student_data
DROP CONSTRAINT student_primary_key;
\d student_data

This is how to drop a primary key using ALTER TABLE in PostgreSQL.
PostgreSQL ALTER TABLE rename PRIMARY KEY
You can rename a PRIMARY KEY in the PostgreSQL by using either of the two statements, ALTER TABLE RENAME CONSTRAINT or ALTER INDEX RENAME. The syntax for both is as follow:
ALTER TABLE table_name
RENAME CONSTRAINT primary_key_constraint_name TO new_key_name;
ALTER INDEX primary_key_constraint_name
RENAME TO new_key_name;
In the above syntax,
- primary_key_constraint_name is the primary key name that can be identified through the statement \d table_name.
- And the new_key_name is the new name given to the PRIMARY KEY.
Example:
\d student_data
ALTER TABLE student_data
RENAME CONSTRAINT student_data_pkey TO student_pkey;
\d student_data
ALTER INDEX student_pkey
RENAME TO stu_dat_pkey;
\d student_data

This is how to rename a primary key using ALTER TABLE in PostgreSQL.
Read How to Restart PostgreSQL (Linux, Windows, Mac)
PostgreSQL ALTER TABLE change PRIMARY KEY
You can change the PRIMARY KEY of a table by, first removing the current PRIMARY KEY by using ALTER TABLE DROP CONSTRAINT statement and then adding a new PRIMARY KEY by using ALTER TABLE ADD PRIMARY KEY statement. The syntax is as below.
ALTER TABLE table_name
DROP CONSTRAINT primary_key_constraint_name; -- by default <table_name>_pkey
ALTER TABLE table_name
ADD PRIMARY KEY (new_candidate_key_name);
In the above syntax, new_candidate_key_name is the name of the column that you want to make the new PRIMARY KEY to the table.
Example:
\d student_data
ALTER TABLE student_data
DROP CONSTRAINT stu_dat_pkey;
\d student_data
ALTER TABLE student_data
ADD PRIMARY KEY (first_name);
\d student_data

This is how to change a primary key using ALTER TABLE in PostgreSQL.
PostgreSQL ALTER TABLE ADD PRIMARY KEY with name
You can also add PRIMARY KEY in PostgreSQL with name by using ALTER TABLE ADD CONSTRAINT PRIMARY KEY and specifying the name of the PRIMARY KEY after ADD CONSTRAINT clause. The syntax is as follow:
ALTER TABLE table_name
ADD CONSTRAINT primary_key_constraint_name
PRIMARY KEY (column_name);
Example:
\d student_data
ALTER TABLE student_data
ADD CONSTRAINT primary_key_of_student_data
PRIMARY KEY (first_name);
\d student_data

PostgreSQL ALTER TABLE ADD PRIMARY KEY IF NOT EXIST
You can also specify the condition while adding a PRIMARY KEY, that if a PRIMARY KEY already exists, to avoid the error caused by duplicating a PRIMARY KEY for a table.
There is no direct method to do it, but you can drop the PRIMARY KEY if exists, and then add a new PRIMARY KEY constraint. The syntax is as follow:
ALTER TABLE table_name -- Bydefault primary key name: <table_name>_pkey
DROP CONSTRAINT IF EXISTS primary_key_constraint_name;
ALTER TABLE table_name
ADD CONSTRAINT new_primary_key_constraint_name PRIMARY KEY (column_name);
Example:
\d student_data
ALTER TABLE student_data
DROP CONSTRAINT IF EXISTS primary_key_of_student_data;
ALTER TABLE student_data
ADD CONSTRAINT student_primary_key PRIMARY KEY (first_name, last_name);
\d student_data

This is how to add a primary key if not exist in PostgreSQL using Alter table.
PostgreSQL ALTER TABLE ADD PRIMARY KEY autoincrement
You can add a column with auto-increment PRIMARY KEY constraint by using ALTER TABLE ADD COLUMN SERIAL. The syntax is as follow:
ALTER TABLE table_name
ADD COLUMN column_name SERIAL PRIMARY KEY; -- PostgreSQL 8.x and above
The above syntax will add a new column with the name column_name with auto-increment PRIMARY KEY constraint.
Now, let’s take a look at the keyword SERIAL:
- It is a pseudo-type, to define auto-increment columns in tables in PostgreSQL.
- It actually creates a Sequence. A sequence is a special kind of database object that generates a sequence of integers in PostgreSQL.
By assigning the SERIAL pseudo-type to any column, PostgreSQL performs the following:
- Firstly, it will create a sequence object and set the next value generated by the sequence as the default value for the column.
- Then, it will add a NOT NULL constraint to that column because a sequence always generates an integer, which is not a null value.
- At last, it will assign that column as the owner of the sequence, as to make the sequence object get deleted itself when that column or table is dropped.
Example:
\d student_data
ALTER TABLE student_data
ADD COLUMN student_id SERIAL PRIMARY KEY;
\d student_data
SELECT * FROM student_data;

You can also add a new column to the existing table with auto-increment PRIMARY KEY constraint using a conventional method (not using SERIAL keyword). The syntax is as follow:
ALTER TABLE table_name
ADD COLUMN column_name INTEGER;
CREATE SEQUENCE sequence_object_name OWNED BY table_name.column_name;
ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT nextval('sequence_object_name');
UPDATE table_name
SET column_name = nextval('sequence_object_name');
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);
In the above syntax,
- Firstly, a new column column_name of integer TYPE is added to the existing table table_name.
- Then, a sequence object is created with the name sequence_object_name by using CREATE SEQUENCE statement and ownership of the sequence object is set to table_name‘s column_name column.
- Then, column column_name‘s DEFAULT is set as the sequence object created before by using the method nextval().
- Then, UPDATE the column_name column values based on the sequence object created.
- And at last, the PRIMARY KEY constraint is added to the table table_name with column column_name as key.
Example:
\d student_data
ALTER TABLE student_data
ADD COLUMN student_id INTEGER;
CREATE SEQUENCE primary_key_sequence OWNED BY student_data.student_id;
ALTER TABLE student_data
ALTER COLUMN student_id SET DEFAULT nextval('primary_key_sequence');
UPDATE student_data
SET student_id = nextval('primary_key_sequence');
ALTER TABLE student_data
ADD PRIMARY KEY (student_id);
\d student_data

This is how to use ALTER TABLE ADD PRIMARY KEY autoincrement in PostgreSQL.
Read: Postgresql drop all tables
PostgreSQL ALTER TABLE ADD PRIMARY KEY using index
Let us see an example of PostgreSQL ALTER TABLE ADD PRIMARY KEY using index.
CREATE UNIQUE INDEX index_name ON table_name(column_name);
ALTER TABLE table_name
ADD CONSTRAINT primary_key_constraint_name PRIMARY KEY
USING INDEX index_name;
In the above syntax,
- Firstly, Create a UNIQUE INDEX specifying the column_name of the table for which you have created the INDEX.
- Then add a PRIMARY KEY constraint by specifying the index_name of the INDEX using the keyword USING.
NOTE – You can’t use a non-unique index for a PRIMARY KEY constraint. And when you are adding a PRIMARY KEY constraint based on an index, you can’t specify column(s) (as they are already defined in the index).
Example:
\d student_data
CREATE UNIQUE INDEX pkey_index ON student_data(first_name);
ALTER TABLE student_data
ADD CONSTRAINT student_data_pkey
PRIMARY KEY USING INDEX pkey_index;
\d student_data

PostgreSQL ALTER TABLE ADD PRIMARY KEY tablespace
You can specify the tablespace of the constraint index when creating a PRIMARY KEY constraint. The syntax is as follow:
ALTER TABLE table_name
ADD CONSTRAINT primary_key_constraint_name PRIMARY KEY (column_name)
USING INDEX TABLESPACE tablespace_name;
Let’s get to know a bit about Tablespace: It allows a superuser to define an alternative storage location on the file system where the data files containing database objects like tables and indexes can be stored.
A privileged user can pass tablespace_name to CREATE DATABASE, CREATE TABLE, CREATE INDEX or ADD CONSTRAINT so that the data files for the given objects are stored within the specified tablespace.
Example:
\db
-- To list all existing tablespaces
\d student_data
ALTER TABLE student_data
ADD CONSTRAINT student_data_pkey PRIMARY KEY (first_name)
USING INDEX TABLESPACE pg_default;
\d student_data

This is an example of ALTER TABLE ADD PRIMARY KEY tablespace in PostgreSQL.
PostgreSQL ALTER TABLE name
You can rename the table by using ALTER TABLE RENAME statement. The syntax is as follow:
ALTER TABLE table_name
RENAME TO new_table_name;
Example:
\d student_data
ALTER TABLE student_data
RENAME TO student_details;
\d student_data
\d student_details

This is an example of the PostgreSQL ALTER TABLE name.
In this PostgreSQL tutorial, we have learned about the PostgreSQL ALTER TABLE statement to modify the structure of an existing table and have covered the following topic:
- PostgreSQL ALTER TABLE
- PostgreSQL ALTER TABLE ADD COLUMN
- PostgreSQL ALTER TABLE RENAME COLUMN
- PostgreSQL ALTER TABLE ALTER COLUMN
- PostgreSQL ALTER TABLE modify column type
- PostgreSQL ALTER TABLE modify column size
- PostgreSQL ALTER TABLE DROP COLUMN
- PostgreSQL ALTER TABLE ADD CONSTRAINT
- PostgreSQL ALTER TABLE DROP CONSTRAINT
- PostgreSQL ALTER TABLE ADD FOREIGN KEY
- PostgreSQL ALTER TABLE ADD PRIMARY KEY
- PostgreSQL ALTER TABLE DROP PRIMARY KEY
- PostgreSQL ALTER TABLE rename PRIMARY KEY
- PostgreSQL ALTER TABLE change PRIMARY KEY
- PostgreSQL ALTER TABLE ADD PRIMARY KEY with name
- PostgreSQL ALTER TABLE ADD PRIMARY KEY IF NOT EXIST
- PostgreSQL ALTER TABLE ADD PRIMARY KEY autoincrement
- PostgreSQL ALTER TABLE ADD PRIMARY KEY using index
- PostgreSQL ALTER TABLE ADD PRIMARY KEY tablespace
- PostgreSQL ALTER TABLE name
For a better understanding of the topic watch my YouTube video:
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.