Open In App

PostgreSQL – ALTER TABLE

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

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:


Last Updated : 28 Aug, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads