Summary: in this tutorial, you will learn how to modify the structure of a table by using the PostgreSQL ALTER TABLE statement.
Introduction to PostgreSQL ALTER TABLE statement
To change existing table structure, you use PostgreSQL ALTER TABLE statement. The syntax of the ALTER TABLE is as follows:
1 | ALTER TABLE table_name action; |
PostgreSQL provides many actions that allow you to:
- Add a column, drop a column, rename a column, or change a column’s data type.
- Set a default value for the column.
- Add a
CHECKconstraint to a column. - Rename a table.
The following illustrates the ALTER TABLE statement variants.
To add a new column to a table, you use ALTER TBLE ADD COLUMN statement:
1 | ALTER TABLE table_name ADD COLUMN new_column_name TYPE; |
To remove an existing column, you use ALTER TABLE DROP COLUMN statement:
1 | ALTER TABLE table_name DROP COLUMN column_name; |
To rename an existing column, you use the ALTER TABLE RENAME COLUMN TO statement:
1 | ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name; |
To change a default value of the column, you use ALTER TABLE ALTER COLUMN SET DEFAULT or DROP DEFAULT:
1 | ALTER TABLE table_name ALTER COLUMN [SET DEFAULT value | DROP DEFAULT] |
To change the NOT NULL constraint, you use ALTER TABLE ALTER COLUMN statement:
1 | ALTER TABLE table_name ALTER COLUMN [SET NOT NULL| DROP NOT NULL] |
To add a CHECK constraint, you use ALTER TABLE ADD CHECK statement:
1 | ALTER TABLE table_name ADD CHECK expression; |
To add a constraint, you use ALTER TABLE ADD CONSTRAINT statement:
1 | ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition |
To rename a table you use ALTER TABLE RENAME TO statement:
1 | ALTER TABLE table_name RENAME TO new_table_name; |
PostgreSQL ALTER TABLE examples
Let’s create a new table named linkfor practicing the ALTER TABLE statement.
1 2 3 4 5 | CREATE TABLE link ( link_id serial PRIMARY KEY, title VARCHAR (512) NOT NULL, url VARCHAR (1024) NOT NULL UNIQUE ); |
To add a new column named active, you use the following statement:
1 | ALTER TABLE link ADD COLUMN active boolean; |
The following statement removes the active column from the link table:
1 | ALTER TABLE link DROP COLUMN active; |
To rename the title column to link_title, you use the following statement:
1 | ALTER TABLE link RENAME COLUMN title TO link_title; |
The following statement adds a new column named target to the link table:
1 | ALTER TABLE link ADD COLUMN target varchar(10); |
To set _blank as the default value for the targetcolumn in the link table, you use the following statement:
1 2 | ALTER TABLE link ALTER COLUMN target SET DEFAULT '_blank'; |
If you insert the new row into the link table without specifying value for the target column, the target column will take _blank as the default value. See the following example:
1 2 3 4 5 6 | INSERT INTO link (link_title, url) VALUES ( 'PostgreSQL Tutorial', 'http://www.postgresqltutorial.com/' ); |
Query data from the linktable:
1 | SELECT * FROM link; |
The following statement adds a CHECK condition to the target column so that the target column only accepts the following values: _self, _blank, _parent, and _top:
1 | ALTER TABLE link ADD CHECK (target IN ('_self', '_blank', '_parent', '_top'); |
If you try to insert a new row that violates the CHECK constraint set for the target column, PostgreSQL will issue an error as shown in the following example:
1 2 | INSERT INTO link(link_title,url,target) VALUES('PostgreSQL','http://www.postgresql.org/','whatever'); |
1 2 | [Err] ERROR: new row for relation "link" violates check constraint "link_target_check" DETAIL: Failing row contains (2, PostgreSQL, http://www.postgresql.org/, null, whatever). |
To rename the name of the link table to url, you use the following statement:
1 | ALTER TABLE link RENAME TO url; |
In this tutorial, we have shown you how to use the PostgreSQL ALTER TABLE statement to change the structure of an existing table.