PostgreSQL ALTER TABLE (original) (raw)

Last Updated : 15 Jul, 2025

In **PostgreSQL, the ALTER TABLE statement is a **powerful and **essential tool that allows us to modify the structure of an existing table to meet evolving **database needs. With **PostgreSQL ALTER TABLE, we can perform various modifications on the table without **disrupting the **ongoing operations of our database.

In this article, we will explain the **core functionalities of the **ALTER TABLE command in **PostgreSQL, providing clear examples and best practices to enhance our **database management. Whether we're **adding or **dropping columns, **renaming them, or **setting constraints, this guide will walk us through each operation step-by-step.

What is PostgreSQL ALTER TABLE?

**PostgreSQL ALTER TABLE is a DDL (Data Definition Language) command used to modify an existing table’s structure. WithALTER TABLE, we can **add new columns, **drop columns, **rename columns, modify **constraints, and much more. These operations are performed while the table remains in use, ensuring **minimal disruption to our **database's functionality.

**Syntax

ALTER TABLE table_name action;

**table_name**is the name of the table we want to modify.
**action**defines the type of modification you wish to perform on the table, such as adding, dropping, or modifying columns

Common Actions with PostgreSQL ALTER TABLE

**PostgreSQLsupports several types of actions that can be performed using the **ALTER TABLE statement. Let’s look into each of these actions with examples.

1. Adding a New Column

To add a new column to an existing table, use the '**ADD COLUMN' action. This is particularly useful when we need to **store additional data in our table.

**Syntax

ALTER TABLE table_name
ADD COLUMN new_column_name TYPE;

**Example:

Suppose we have a table named '**links', and we want to add a new boolean column named '**active':

ALTER TABLE links
ADD COLUMN active BOOLEAN;

2. Dropping a Column

If a column is no longer needed, you can remove it using the 'DROP COLUMN****'** action. This helps keep your **table structure clean and relevant to **current data requirements.

**Syntax

ALTER TABLE table_name
DROP COLUMN column_name;

**Example:

For instance, to remove the '**active' column from the '**links' table:

ALTER TABLE links
DROP COLUMN active;

3. Renaming a Column

To rename a column, use the 'RENAME COLUMN****'** action. This is useful when the column name needs to better reflect the data it holds.

**Syntax

ALTER TABLE table_name
RENAME COLUMN column_name TO new_column_name;

**Example:

To rename the '**title' column to '**link_title' in the '**links' table:

ALTER TABLE links
RENAME COLUMN title TO link_title;

4. Changing the Default Value of a Column

We can modify the default value of a column using the '**ALTER COLUMN' action. This is helpful when the default value needs to be **updated to meet new business rules.

**Syntax

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

**Example:

To set the default value of the 'target'column to '_blank':

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

5. Changing NOT NULL Constraint

To enforce that a column must have a value, we can add a **NOT NULL constraint. Conversely, we can drop this constraint if it’s no longer required.

**Syntax

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

To remove the NOT NULL constraint, use:

ALTER TABLE table_name
ALTER COLUMN column_name
DROP NOT NULL;

**Example:

To add a NOT NULL constraint to the target column:

ALTER TABLE links
ALTER COLUMN target SET NOT NULL;

6. Adding a CHECK Constraint

**CHECK constraints allow you to define a condition that the data in a column must meet. This ensures data integrity by restricting the values entered into the column.

**Syntax

ALTER TABLE table_name
ADD CHECK expression;

**Example:

To add a **CHECK constraint ensuring that the url column must start with 'http':

ALTER TABLE links
ADD CONSTRAINT url_check CHECK (url LIKE 'http%');

7. Adding a General Constraint

We can add constraints such as **UNIQUE, **PRIMARY KEY, or **FOREIGN KEY to ensure data integrity and enforce relational rules in our database.

**Syntax

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_definition;

**Example:

To add a **PRIMARY KEY constraint to the **link_id**column:

ALTER TABLE links
ADD CONSTRAINT link_id_pk PRIMARY KEY (link_id);

8. Renaming a Table

If we need to change the name of an entire table, we can do so using the RENAME TO action.

**Syntax

ALTER TABLE table_name
RENAME TO new_table_name;

**Example:

If we want to rename the **links table to **website_links:

ALTER TABLE links RENAME TO website_links;

Example of PostgreSQL ALTER TABLE

Now let's look into an example. For the same purpose let's first create a table (say, '**links') that we can alter in our example using the below statement:

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

Now, let’s add a new column named '**target' to this table:

ALTER TABLE links
ADD COLUMN target VARCHAR(10);

Next, we’ll set '**_blank' as the default value for the '**target' column:

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

Let’s insert some data into the '**links' table:

INSERT INTO links (link_title, url)
VALUES ('PostgreSQL Tutorial', 'https://www.geeksforgeeks.org/');

Finally, to check the data in the '**links' table, use:

SELECT * FROM links;

**Output

PostgreSQL ALTER TABLE Example

Important Points About PostgreSQL ALTER TABLE Statement

Consider using 'ALTER TABLE ... ADD COLUMN' without a default value first, and then setting the default in a separate statement to avoid locking the entire table for long periods.
When adding constraints to a table with existing data, PostgreSQL checks the current data against the new constraint. If the data doesn’t satisfy the constraint, the **ALTER TABLE statement will fail.
While we can add or drop constraints using **ALTER TABLE, modifying an existing constraint directly is not supported. Instead, we must drop the existing constraint and add a new one.
The **ALTER TABLE statement can be applied to both permanent and temporary tables. However, changes to temporary tables are only valid within the session that created them.

Conclusion

The **PostgreSQL ALTER TABLE statement is an essential command for efficiently managing and modifying table structures in PostgreSQL databases. Whether we're **adding, **removing, or **renaming columns, or setting constraints to enforce **data integrity, the **ALTER TABLE statement provides the **flexibility to adapt to our evolving database needs without **disrupting operations.