PostgreSQL ALTER TABLE Statement

Summary: In this tutorial, you will learn how to enhance a table’s structure using the PostgreSQL ALTER TABLE statement.

Getting Started with PostgreSQL ALTER TABLE statement #

In PostgreSQL, the ALTER TABLE statement allows you to modify the table structure effectively.

Here’s the basic syntax of the ALTER TABLE statement:

ALTER TABLE table_name
action;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, specify the table name you want to modify the structure.
Second, provide an action you want to perform.

Here are the main actions:

Renaming a table.
Rename a column.
Adding one or more columns to the table.
Removing a column.
Applying a constraint to a column.
Changing the data type of a column.

Suppose we have the following materials table that stores the product information:

CREATE TABLE materials (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  status bool NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Renaming a table #

You use the ALTER TABLE ... RENAME TO statement to rename a table:

ALTER TABLE table_name
RENAME TO new_table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, specify the name of the table in the ALTER TABLE clause.
Second, define the new table name in the RENAME TO clause.

The following example uses the ALTER TABLE ... RENAME TO to rename the materials table to products:

ALTER TABLE materials
RENAME TO products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Renaming columns #

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

ALTER TABLE table_name
RENAME COLUMN column_name
TO new_column_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following example rename the status column to active:

ALTER TABLE products
RENAME COLUMN status
TO active;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Adding new columns to a table #

Use the ALTER TABLE...ADD COLUMN statement to add a new column to a table:

ALTER TABLE table_name
ADD COLUMN column_name data_type constraint;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, provide the name of the table you want to modify.
Second, define the column name, type, and constraint in the ADD COLUMN clause.

For example, the following statement adds the price column to the products table:

ALTER TABLE products
ADD COLUMN price DEC(5, 2) NOT NULL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

It’s possible to add multiple columns using the ALTER TABLE statement.

For example, the following ALTER TABLE ... ADD COLUMN statement adds the weight, volume, and model_no columns to the products table:

ALTER TABLE products
ADD COLUMN weight DEC(5, 2),
ADD COLUMN volume DEC(5, 2),
ADD COLUMN model_no VARCHAR(25);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Changing the column data type #

You use the ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE statement to change the data type of a column:

ALTER TABLE table_name
ALTER COLUMN column_name
SET DATA TYPE new_data_type;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, specify the name of the table in the ALTER TABLE clause.
Second, provide the name of the column you want to modify.
Third, set the new data type in the SET DATA TYPE clause.

If the table has data, you must ensure the new type is compatible with the existing data.

For example, the following statement extends the size of the price column in the products table to DEC(11,2):

ALTER TABLE products
ALTER COLUMN price
SET DATA TYPE DEC(11, 2);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Applying constraints to columns #

You use the ALTER TABLE ... ALTER COLUMN SET statement to add a constraint to a column.

NOT NULL constraint #

The following statement applies a NOT NULL constraint to a column:

ALTER TABLE table_name
ALTER COLUMN column_name
SET NOT NULL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following statement adds a NOT NULL constraint to the weight and volume columns of the products table:

ALTER TABLE products
ALTER COLUMN weight SET NOT NULL,
ALTER COLUMN volume SET NOT NULL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

The weight and column must not have any NULL values before you run the statement.

CHECK constraint #

The following statement adds a CHECK constraint to a column:

ALTER TABLE table_name
ALTER COLUMN column_name
ADD CHECK expression;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following statement adds a CHECK constraint to the weight and volume columns of the products table:

ALTER TABLE products
ADD CHECK (weight > 0),
ADD CHECK (volume > 0);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

DEFAULT constraint #

The following statement sets a default value for a column using a DEFAULT constraint:

ALTER TABLE table_name
ALTER COLUMN column_name
SET DEFAULT default_value;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following statement sets the value for the active column to true:

ALTER TABLE products
ALTER COLUMN active
SET DEFAULT true;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

UNIQUE constraint #

The following statement adds a unique constraint for one or more columns:

ALTER TABLE table_name
ADD UNIQUE (column1, column2, ...);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following statement adds a unique constraint to the model_no of the products table:

ALTER TABLE products
ADD UNIQUE (model_no);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Dropping columns #

The ALTER TABLE ... DROP COLUMN removes a column from a table:

ALTER TABLE table_name
DROP COLUMN column_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following statement drops the active column of the products table:

ALTER TABLE products
DROP COLUMN active;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Summary #

Use the ALTER TABLE statement to change the structure of a table.

Quiz #

Was this tutorial helpful ?