Introduction to PostgreSQL ALTER TABLE statement

To change the structure of an existing table, you use PostgreSQLALTER TABLE statement.

The following illustrates the basic syntax of theALTER TABLE statement:

ALTER TABLE table_name action;

PostgreSQL provides you with many actions:

• Add a column
• Drop a column
• Change the data type of a column
• Rename a column
• Set a default value for the column
• Add a constraint to a column.
• Rename a table

To add a new column to a table, you useALTER TABLE ADD COLUMN statement:

ALTER TABLE table_nameADD COLUMN column_name datatype column_constraint;

To drop a column from a table, you useALTER TABLE DROP COLUMN statement:

ALTER TABLE table_nameDROP COLUMN column_name;

To rename a column, you use the[ALTER TABLE RENAME COLUMN](postgresql-rename-column) TO statement:

ALTER TABLE table_nameRENAME COLUMN column_nameTO new_column_name;

To change a default value of the column, you useALTER TABLE ALTER COLUMN SET DEFAULT or  DROP DEFAULT:

ALTER TABLE table_nameALTER COLUMN column_name[SET DEFAULT value | DROP DEFAULT];

To change theNOT NULL constraint, you useALTER TABLE ALTER COLUMN statement:

ALTER TABLE table_nameALTER COLUMN column_name[SET NOT NULL| DROP NOT NULL];

To add aCHECK constraint, you useALTER TABLE ADD CHECK statement:

ALTER TABLE table_nameADD CHECK expression;

Generally, to add a constraint to a table, you useALTER TABLE ADD CONSTRAINT statement:

ALTER TABLE table_nameADD CONSTRAINT constraint_name constraint_definition;

Torename a table you useALTER TABLE RENAME TO statement:

ALTER TABLE table_nameRENAMETO new_table_name;

PostgreSQL ALTER TABLE examples

Let’screate a new table calledlinks for practicing with theALTER TABLE statement.

DROP TABLE IF EXISTS links;CREATE TABLE links (   link_idserial PRIMARY KEY,   titleVARCHAR (512)NOT NULL,   url VARCHAR (1024)NOT NULL);

Toadd a new column namedactive, you use the following statement:

ALTER TABLE linksADD COLUMN activeboolean;

The following statement removes theactivecolumn from thelinkstable:

ALTER TABLE linksDROP COLUMN active;

To change the name of thetitle column to link_title, you use the following statement:

ALTER TABLE linksRENAME COLUMN titleTO link_title;

The following statement adds a new column namedtargetto thelinkstable:

ALTER TABLE linksADD COLUMNtarget VARCHAR(10);

To set _blank as the default value for thetargetcolumn in thelinkstable, you use the following statement:

ALTER TABLE linksALTER COLUMNtargetSET DEFAULT '_blank';

If youinsert the new row into thelinks table without specifying a value for thetarget column, thetarget column will take the _blank as the default value. For example:

INSERT INTO links (link_title,url)VALUES('PostgreSQL Tutorial','https://neon.com/postgresql/');

The following statement selects data from thelinks table:

SELECT * FROM links;

The following statement adds aCHECKcondition to thetargetcolumn so that thetargetcolumn only accepts the following values:_self,_blank,_parent, and_top:

ALTER TABLE linksADD CHECK (target IN ('_self','_blank','_parent','_top'));

If you attempt to insert a new row that violates theCHECK constraint set for thetargetcolumn, PostgreSQL will issue an error as shown in the following example:

INSERT INTO links(link_title,url,target)VALUES('PostgreSQL','http://www.postgresql.org/','whatever');

ERROR:  newrow for relation"links" violatescheck constraint "links_target_check"DETAIL:  Failingrow contains (2, PostgreSQL,http://www.postgresql.org/, whatever).DETAIL:  Failingrow contains (2, PostgreSQL,http://www.postgresql.org/, whatever).

The following statement adds aUNIQUE constraint to theurl column of thelinks table:

ALTER TABLE linksADD CONSTRAINT unique_url UNIQUE (url );

The following statement attempts to insert the url that already exists:

INSERT INTO links(link_title,url)VALUES('PostgreSQL','https://neon.com/postgresql/');

It causes an error due to the unique_url constraint:

ERROR:  duplicatekey value violatesunique constraint "unique_url"DETAIL:Key (url)=(/postgresql/) alreadyexists.

The following statement changes the name of thelinks table tourls:

ALTER TABLE linksRENAME TO urls;

In this tutorial, you have learned how to use the PostgreSQLALTER TABLE statement to change the structure of an existing table.

Last updated onMarch 26, 2024

On this page

• Introduction to PostgreSQL ALTER TABLE statement
• PostgreSQL ALTER TABLE examples