PostgreSQL Tutorial

  • Home
  • Administration
  • Views
  • Triggers
  • Stored Procedures
  • Interfaces
    • PostgreSQL PHP
    • PostgreSQL Python
    • PostgreSQL JDBC
Home / PostgreSQL Tutorial / PostgreSQL ALTER TABLE

PostgreSQL ALTER TABLE

Summary: in this tutorial, you will learn how to alter existing table structure by using the PostgreSQL ALTER TABLE statement.

Introduction to PostgreSQL ALTER TABLE

To change existing table structure, you use PostgreSQL ALTER TABLE statement. The syntax of the ALTER TABLE is as follows:

1
ALTER TABLE table_name action;

PostgreSQL provides many actions that allow you to:

  • Add, remove, or rename column.
  • Set default value for the column.
  • Add CHECK constraint to a column.
  • Rename table

The following illustrates the ALTER TABLE statement variants.

To add a new column into the table, you use ALTER TBLE ADD COLUMN statement:

1
ALTER TABLE table_name ADD COLUMN new_column_name TYPE;

To remove an existing column, you use ALTER TABLE DROP COLUMN statement:

1
ALTER TABLE table_name DROP COLUMN column_name;

To rename an existing column, you use the ALTER TABLE RENAME COLUMN TO statement:

1
ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

To change default value of the column, you use ALTER TABLE ALTER COLUMN SET DEFAULT or DROP DEFAULT:

1
ALTER TABLE table_name ALTER COLUMN [SET DEFAULT value | DROP DEFAULT]

To change the NOT NULL constraint, you use ALTER TABLE ALTER COLUMN statement:

1
ALTER TABLE table_name ALTER COLUMN [SET NOT NULL| DROP NOT NULL]

To add a CHECKconstraint, you use ALTER TABLE ADD CHECK statement:

1
ALTER TABLE table_name ADD CHECK expression;

To add a constraint, you use ALTER TABLE ADD CONSTRAINT statement:

1
ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition

To rename the table you use ALTER TABLE RENAME TO statement:

1
ALTER TABLE table_name RENAME TO new_table_name;

PostgreSQL ALTER TABLE examples

Let’s create a new table named linkfor practicing the ALTER TABLE statement.

1
2
3
4
5
CREATE TABLE link (
link_id serial PRIMARY KEY,
title VARCHAR (512) NOT NULL,
url VARCHAR (1024) NOT NULL UNIQUE
);

To add a new column named active, you use the following statement:

1
ALTER TABLE link ADD COLUMN active boolean;

The following statement removes the activecolumn from the linktable:

1
ALTER TABLE link DROP COLUMN active;

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

1
ALTER TABLE link RENAME COLUMN title TO link_title;

The following statement adds a new column named targetto the linktable:

1
ALTER TABLE link ADD COLUMN target varchar(10);

To set _blankas the default value for the targetcolumn in the linktable, you use the following statement:

1
2
ALTER TABLE link ALTER COLUMN target
SET DEFAULT '_blank';

If you insert the new row into the linktable without specifying value for the target column, the targetcolumn will take _blankas the default value.

Insert a new row into the linktable:

1
2
INSERT INTO link(link_title,url)
VALUES('PostgreSQL Tutorial','http://www.postgresqltutorial.com/');

Query data from the linktable:

1
SELECT  * FROM link;

PostgreSQL ALTER TABLE SET DEFAULT

The following statement adds a CHECKcondition to the targetcolumn so that the targetcolumn only accepts the following values: _self, _blank, _parent, and _top:

1
ALTER TABLE link ADD CHECK (target IN ('_self', '_blank', '_parent', '_top');

If you try to insert a new row that violates the CHECKconstraint set for the targetcolumn.

1
2
INSERT INTO link(link_title,url,target)
VALUES('PostgreSQL','http://www.postgresql.org/','whatever');

PostgreSQL issues an error:

1
2
[Err] ERROR:  new row for relation "link" violates check constraint "link_target_check"
DETAIL:  Failing row contains (2, PostgreSQL, http://www.postgresql.org/, null, whatever).

To rename the name of the link table to url, you use the following statement:

1
ALTER TABLE link RENAME TO url;

In this tutorial, we have shown you how to use the PostgreSQL ALTER TABLE statement to change existing table structure.

Related Tutorials

  • Using PostgreSQL ADD COLUMN to Add One or More Columns To a Table
Previous Tutorial: PostgreSQL CREATE TABLE
Next Tutorial: Using PostgreSQL ADD COLUMN to Add One or More Columns To a Table

PostgreSQL Quick Start

  • What is PostgreSQL?
  • Install PostgreSQL
  • Connect to Database
  • Download PostgreSQL Sample Database
  • Load Sample Database
  • Explore Server and Database Objects

PostgreSQL Fundamentals

  • PostgreSQL Select
  • PostgreSQL Order By
  • PostgreSQL Select Distinct
  • PostgreSQL Where
  • PostgreSQL LIMIT
  • PostgreSQL IN
  • PostgreSQL Between
  • PostgreSQL Like
  • PostgreSQL Inner Join
  • PostgreSQL Left Join
  • PostgreSQL Full Outer Join
  • PostgreSQL Cross Join
  • PostgreSQL Natural Join
  • PostgreSQL Group By
  • PostgreSQL Having
  • PostgreSQL Union
  • PostgreSQL Intersect
  • PostgreSQL Except
  • PostgreSQL Subquery
  • PostgreSQL Insert
  • PostgreSQL Update
  • PostgreSQL Delete
  • PostgreSQL Data Types
  • PostgreSQL Create Table
  • PostgreSQL Alter Table
  • PostgreSQL Drop Table
  • PostgreSQL Truncate Table
  • PostgreSQL CHECK Constraint
  • PostgreSQL Not-Null Constraint
  • PostgreSQL Foreign Key
  • PostgreSQL Primary Key
  • PostgreSQL UNIQUE Constraint

About PostgreSQL Tutorial

PostgreSQLTutorial.com is a website dedicated to developers and database administrators who are working on PostgreSQL database management system.

We constantly publish useful PostgreSQL tutorials to keep you up-to-date with the latest PostgreSQL features and technologies. All PostgreSQL tutorials are simple, easy-to-follow and practical.

Recent PostgreSQL Tutorials

  • PostgreSQL Recursive View
  • Learn PostgreSQL Recursive Query By Example
  • Creating Updatable Views Using the WITH CHECK OPTION Clause
  • PostgreSQL Upsert Using INSERT ON CONFLICT statement
  • How to Generate a Random Number in A Range
  • Using PostgreSQL ADD COLUMN to Add One or More Columns To a Table
  • PostgreSQL Character Types: CHAR, VARCHAR, and TEXT
  • Using PostgreSQL SERIAL To Create Auto-increment Column
  • PostgreSQL Boolean Data Type with Practical Examples
  • Understanding PostgreSQL Timestamp Data Types

More Tutorials

  • PostgreSQL PHP
  • PostgreSQL Python
  • PostgreSQL JDBC
  • PostgreSQL Functions
  • PostgreSQL Resources

Site Info

  • Home
  • About Us
  • Contact Us
  • Privacy Policy

Copyright © 2016 by PostgreSQL Tutorial Website. All Rights Reserved.