PostgreSQL – ALTER TABLE

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. With ALTER 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

PostgreSQL supports 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

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.

FAQs

How do I alter the table in Postgres?

Use the ALTER TABLE command to modify an existing table in PostgreSQL. This can add, delete, or modify columns, change data types, or set constraints.

How do I edit a table in PostgreSQL?

To edit a table, use ALTER TABLE for structural changes and UPDATE for modifying specific data within the table.

How to alter data in PostgreSQL?

Use the UPDATE statement to change existing data in PostgreSQL tables, specifying the table name, columns, and new values where needed.

R

RajuKumar19

Follow

Improve

Article Tags :

• Databases
• PostgreSQL
• postgreSQL-managing-table