Alter Table PostgreSQL

PostgreSQL allows you to modify database objects such as tables, databases, schemas, group, users, and more. In this tutorial, we will focus on how you can ALTER the structure of a table.

Basic Usage

The PostgreSQL ALTER query is pretty straightforward. It follows the simple syntax below:

ALTER TABLE table_name TASK;

In this case, the action includes the supported PostgreSQL operations. These include:

ADD or DELETE a column
MODIFY column data type
RENAME a table
RENAME a column
ADD column constraint
SET or DROP column DEFAULT value.
DROP IDENTITY
ADD table constraint
ALTER CONSTRAINT
ATTACHING PARTITIONS
SET schema

And many more. Learn more about ALTER TABLE operations in the documentation.

Below are example operations you can perform on a table using the ALTER TABLE query.

Add or Drop Column

One of the most common use cases of the ALTER TABLE command is to add a new column to the table.

The syntax is as:

ALTER TABLE table_name ADD COLUMN column_name data_type constraint;

The query above will append the column with the specified column name, data type, and constraints to the table.

To drop a column, you can use the DROP COLUMN query as:

ALTER TABLE table_name DROP COLUMN IF EXISTS column_name;

Modify Column Data type

Another use of the ALTER TABLE command is to change a column data type. An example query for that is:

ALTER TABLE table_name ALTER COLUMN column_name TYPE VARCHAR(255);

In the above example, we use the ALTER commands (TABLE & COLUMN) to change the data type to a VARCHAR.

Rename a Table

We can also use the ALTER TABLE query to rename a table. The syntax for that is:

ALTER TABLE old_table_name RENAME TO new_table_name;

Rename a Column

We can also use ALTER to rename a column by expressing the query as:

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

Add/Remove Column Default Value

To add or remove a column constraint, we can use the query:

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT “value”;

To drop a default value:

ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

Add Table Constraint

To add a table constraint using the ALTER TABLE command, we can do:

ALTER TABLE table_name ADD CONSTRAINT constraint_name, constraint_def;

In the above example, we specify the column name and column definition after the ADD constraint query.

Alter Constraint

To alter an existing table constraint, we can use the query:

ALTER TABLE table_name ALTER CONSTRAINT constraint_name;

Add Check Condition

To set a column that accepts only specific values, we can set a check condition with target values.

Here is an example query:

ALTER TABLE table_name ADD CHECK (column_name IN (check_var1, check_var2, check_var3, check_varn));

The above condition ensures that the specified column only contains the specified values.

Set Table Schema

To change the table schema, use the query:

ALTER TABLE current_schema.table_name SET SCHEMA another_schema;

Remove Not Null Constraint

You can also remove a not null constraint from a column using the query:

ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;

Rename Existing Constraint

You can also use the ALTER TABLE to rename an existing constraint. The general syntax for that is:

ALTER TABLE table_name RENAME CONSTRAINT old_constraint_name TO new_constraint_name.

Closing

The above examples show how you can use the ALTER TABLE command to change a table’s structure. Although we have provided the syntax for the most common operations, there are more actions you can do.

I would recommend checking the official documentation for other actions.