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.w3wiki.net/');
Query data from the links table:
SELECT * FROM links;
Output:
Contact Us