PostgreSQL – ALTER TABLE

In PostgreSQL, the structure of an existing table can be modified using the ALTER TABLE statement.

Syntax: ALTER TABLE table_name action;

PostgreSQL supports the various actions to perform with ALTER TABLE as listed below:

• Add a column to an existing table as below:

  ALTER TABLE table_name ADD COLUMN new_column_name TYPE;

• Drop a column from an existing table as below:

  ALTER TABLE table_name DROP COLUMN column_name;

• Rename a column from an existing table as below:

  ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

• Change the default value of a column from an existing table as below:

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

• Change NOT NULL constraint as below:

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

• Add CHECK constraints to a column as below:

  ALTER TABLE table_name ADD CHECK expression;

• Add a constraint as below:

  ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition;

• Rename an existing table as below:

  ALTER TABLE table_name RENAME TO new_table_name;

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

Example:
In this example, we will insert a new column to the links table as below:

ALTER TABLE links
ADD COLUMN active boolean;

The following statement removes the active column from the links table:

ALTER TABLE links 
DROP COLUMN active;

To rename the title column to link_title, you use the following statement:

ALTER TABLE links 
RENAME COLUMN title TO link_title;

The following statement adds a new column named target to the links table:

ALTER TABLE links 
ADD COLUMN target VARCHAR(10);

To set _blank as the default value for the target column in the links table, you use the following statement:

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

If you insert the new row into the links table without specifying a value for the target column, the target column will take _blank as the default value. For demonstration let’s insert some data as follows:

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

Query data from the links table:

SELECT * FROM links;

Output: