PostgreSQL ALTER TABLE + 19 Examples

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.

Read PostgreSQL ADD COLUMN

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: