PostgreSQL Tutorial: ALTER TABLE

August 2, 2023

Summary: In this tutorial, you will learn how to use the PostgreSQL ALTER TABLE statement to modify the structure of a table.

Table of Contents

Introduction to PostgreSQL ALTER TABLE statement

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

The following illustrates the basic syntax of the ALTER TABLE statement:

ALTER TABLE table_name action;

PostgreSQL provides you with many actions:

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

ALTER TABLE table_name 
ADD COLUMN column_name datatype column_constraint;

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

ALTER TABLE table_name 
DROP COLUMN column_name;

To rename a column, you use the ALTER TABLE RENAME COLUMN TO statement:

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:

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

To change the NOT NULL constraint, you use ALTER TABLE ALTER COLUMN statement:

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

To add a CHECK constraint, you use ALTER TABLE ADD CHECK statement:

ALTER TABLE table_name 
ADD CHECK expression;

Generailly, to add a constraint to a table, you use ALTER TABLE ADD CONSTRAINT statement:

ALTER TABLE table_name 
ADD CONSTRAINT constraint_name constraint_definition;

To rename a table you use ALTER TABLE RENAME TO statement:

ALTER TABLE table_name 
RENAME TO new_table_name;

PostgreSQL ALTER TABLE examples

Let’s create a new table calledlinks for practicing with the ALTER TABLE statement.

DROP TABLE IF EXISTS links;

CREATE TABLE links (
   link_id serial PRIMARY KEY,
   title VARCHAR (512) NOT NULL,
   url VARCHAR (1024) NOT NULL
);

To add a new column named active, you use the following statement:

ALTER TABLE links
ADD COLUMN active boolean;

The following statement removes the activecolumn from the linkstable:

ALTER TABLE links 
DROP COLUMN active;

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

ALTER TABLE links 
RENAME COLUMN title TO link_title;

The following statement adds a new column named targetto the linkstable:

ALTER TABLE links 
ADD COLUMN target VARCHAR(10);

To set _blank as the default value for the targetcolumn in the linkstable, you use the following statement:

ALTER TABLE links 
ALTER COLUMN target
SET DEFAULT '_blank';

If you insert the new row into the links table without specifying a value for the target column, the targetcolumn will take the _blank as the default value. For example:

INSERT INTO links (link_title, url)
VALUES('PostgreSQL Tutorial','https://www.rockdata.net/tutorial/');

The following statement selects data from the links table:

SELECT * FROM links;

PostgreSQL ALTER TABLE SET DEFAULT

The following statement adds a CHECKcondition to the targetcolumn so that the targetcolumn only accepts the following values: _self, _blank, _parent, and _top:

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

If you attempt to insert a new row that violates the CHECK constraint set for the targetcolumn, 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:  new row for relation "links" violates check constraint "links_target_check"
DETAIL:  Failing row contains (2, PostgreSQL, http://www.postgresql.org/, whatever).

The following statement adds a UNIQUE constraint to the url column of the links table:

ALTER TABLE links 
ADD 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://www.rockdata.net/tutorial/');

It causes an error due to the unique_url constraint:

ERROR:  duplicate key value violates unique constraint "unique_url"
DETAIL:  Key (url)=(https://www.rockdata.net/tutorial/) already exists.

The following statement changes the name of the links table to urls:

ALTER TABLE links 
RENAME TO urls;

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

Search

Categories

Tags