Home » PostgreSQL Tutorial » PostgreSQL ALTER TABLE Statement

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;

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:

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
);

Renaming a table #

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

ALTER TABLE table_name
RENAME TO new_table_name;

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;

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;

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

ALTER TABLE products
RENAME COLUMN status
TO active;

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;

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;

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);

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;

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);

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;

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;

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;

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);

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;

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

ALTER TABLE products
ALTER COLUMN active
SET DEFAULT true;

Try it

UNIQUE constraint #

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

ALTER TABLE table_name
ADD UNIQUE (column1, column2, ...);

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

ALTER TABLE products
ADD UNIQUE (model_no);

Try it

Dropping columns #

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

ALTER TABLE table_name
DROP COLUMN column_name;

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

ALTER TABLE products
DROP COLUMN active;

Try it

Summary #

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

Quiz #

Quiz

PostgreSQL ALTER TABLE Statement

10 questions

To help you understand how to enhance a table’s structure using the PostgreSQL ALTER TABLE statement.

Was this helpful?