Summary: in this tutorial, you will learn how to alter existing table structure by using the PostgreSQL ALTER TABLE statement.
Introduction to PostgreSQL ALTER TABLE
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, remove, or rename column.
- Set default value for the column.
- Add CHECK constraint to a column.
- Rename table
The following illustrates the ALTER TABLE statement variants.
To add a new column into the 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 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 CHECKconstraint, 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 the 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 activecolumn from the linktable:
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 targetto the linktable:
1 | ALTER TABLE link ADD COLUMN target varchar(10); |
To set _blankas the default value for the targetcolumn in the linktable, you use the following statement:
1 2 | ALTER TABLE link ALTER COLUMN target SET DEFAULT '_blank'; |
If you insert the new row into the linktable without specifying value for the target column, the targetcolumn will take _blankas the default value.
Insert a new row into the linktable:
1 2 | 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 CHECKcondition to the targetcolumn so that the targetcolumn 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 CHECKconstraint set for the targetcolumn.
1 2 | INSERT INTO link(link_title,url,target) VALUES('PostgreSQL','http://www.postgresql.org/','whatever'); |
PostgreSQL issues an error:
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 existing table structure.