NAME¶

ALTER_TABLE - change the definition of a table

SYNOPSIS¶

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]


    action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]


    RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]


    RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] name


    RENAME TO new_name
ALTER TABLE [ IF EXISTS ] name


    SET SCHEMA new_schema
where action is one of:


    ADD [ COLUMN ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]


    DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]


    ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]


    ALTER [ COLUMN ] column_name SET DEFAULT expression


    ALTER [ COLUMN ] column_name DROP DEFAULT


    ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL


    ALTER [ COLUMN ] column_name SET STATISTICS integer


    ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )


    ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )


    ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }


    ADD table_constraint [ NOT VALID ]


    ADD table_constraint_using_index


    VALIDATE CONSTRAINT constraint_name


    DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]


    DISABLE TRIGGER [ trigger_name | ALL | USER ]


    ENABLE TRIGGER [ trigger_name | ALL | USER ]


    ENABLE REPLICA TRIGGER trigger_name


    ENABLE ALWAYS TRIGGER trigger_name


    DISABLE RULE rewrite_rule_name


    ENABLE RULE rewrite_rule_name


    ENABLE REPLICA RULE rewrite_rule_name


    ENABLE ALWAYS RULE rewrite_rule_name


    CLUSTER ON index_name


    SET WITHOUT CLUSTER


    SET WITH OIDS


    SET WITHOUT OIDS


    SET ( storage_parameter = value [, ... ] )


    RESET ( storage_parameter [, ... ] )


    INHERIT parent_table


    NO INHERIT parent_table


    OF type_name


    NOT OF


    OWNER TO new_owner


    SET TABLESPACE new_tablespace
and table_constraint_using_index is:


    [ CONSTRAINT constraint_name ]


    { UNIQUE | PRIMARY KEY } USING INDEX index_name


    [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

DESCRIPTION¶

ALTER TABLE changes the definition of an existing table. There are several subforms:

ADD COLUMN

DROP COLUMN [ IF EXISTS ]

IF EXISTS

SET DATA TYPE

SET/DROP DEFAULT

SET/DROP NOT NULL

SET STATISTICS

SET ( attribute_option = value [, ... ] ), RESET ( attribute_option [, ... ] )

SET STORAGE

ADD table_constraint [ NOT VALID ]

ADD table_constraint_using_index

VALIDATE CONSTRAINT

DROP CONSTRAINT [ IF EXISTS ]

DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER

DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE

CLUSTER ON

SET WITHOUT CLUSTER

SET WITH OIDS

SET WITHOUT OIDS

SET ( storage_parameter = value [, ... ] )

RESET ( storage_parameter [, ... ] )

INHERIT parent_table

NO INHERIT parent_table

OF type_name

NOT OF

OWNER

SET TABLESPACE

RENAME

SET SCHEMA

All the actions except RENAME and SET SCHEMA can be combined into a list of multiple alterations to apply in parallel. For example, it is possible to add several columns and/or alter the type of several columns in a single command. This is particularly useful with large tables, since only one pass over the table need be made.

You must own the table to use ALTER TABLE. To change the schema of a table, you must also have CREATE privilege on the new schema. To add the table as a new child of a parent table, you must own the parent table as well. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the table's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the table. However, a superuser can alter ownership of any table anyway.) To add a column or alter a column type or use the OF clause, you must also have USAGE privilege on the data type.

PARAMETERS¶

name

column_name

new_column_name

new_name

type

table_constraint

constraint_name

CASCADE

RESTRICT

trigger_name

ALL

USER

index_name

storage_parameter

value

parent_table

new_owner

new_tablespace

new_schema

NOTES¶

The key word COLUMN is noise and can be omitted.

When a column is added with ADD COLUMN, all existing rows in the table are initialized with the column's default value (NULL if no DEFAULT clause is specified).

Adding a column with a non-null default or changing the type of an existing column will require the entire table and indexes to be rewritten. As an exception, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed, but any indexes on the affected columns must still be rebuilt. Adding or removing a system oid column also requires rewriting the entire table. Table and/or index rebuilds may take a significant amount of time for a large table; and will temporarily require as much as double the disk space.

Adding a CHECK or NOT NULL constraint requires scanning the table to verify that existing rows meet the constraint, but does not require a table rewrite.

The main reason for providing the option to specify multiple changes in a single ALTER TABLE is that multiple table scans or rewrites can thereby be combined into a single pass over the table.

The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated. (These statements do not apply when dropping the system oid column; that is done with an immediate rewrite.)

To force immediate reclamation of space occupied by a dropped column, you can execute one of the forms of ALTER TABLE that performs a rewrite of the whole table. This results in reconstructing each row with the dropped column replaced by a null value.

The rewriting forms of ALTER TABLE are not MVCC-safe. After a table rewrite, the table will appear empty to concurrent transactions, if they are using a snapshot taken before the rewrite occurred. See Section 13.5, “Caveats”, in the documentation for more details.

The USING option of SET DATA TYPE can actually specify any expression involving the old values of the row; that is, it can refer to other columns as well as the one being converted. This allows very general conversions to be done with the SET DATA TYPE syntax. Because of this flexibility, the USING expression is not applied to the column's default value (if any); the result might not be a constant expression as required for a default. This means that when there is no implicit or assignment cast from old to new type, SET DATA TYPE might fail to convert the default even though a USING clause is supplied. In such cases, drop the default with DROP DEFAULT, perform the ALTER TYPE, and then use SET DEFAULT to add a suitable new default. Similar considerations apply to indexes and constraints involving the column.

If a table has any descendant tables, it is not permitted to add, rename, or change the type of a column, or rename an inherited constraint in the parent table without doing the same to the descendants. That is, ALTER TABLE ONLY will be rejected. This ensures that the descendants always have columns matching the parent.

A recursive DROP COLUMN operation will remove a descendant table's column only if the descendant does not inherit that column from any other parents and never had an independent definition of the column. A nonrecursive DROP COLUMN (i.e., ALTER TABLE ONLY ... DROP COLUMN) never removes any descendant columns, but instead marks them as independently defined rather than inherited.

The TRIGGER, CLUSTER, OWNER, and TABLESPACE actions never recurse to descendant tables; that is, they always act as though ONLY were specified. Adding a constraint recurses only for CHECK constraints that are not marked NO INHERIT.

Changing any part of a system catalog table is not permitted.

Refer to CREATE TABLE (CREATE_TABLE(7)) for a further description of valid parameters. Chapter 5, Data Definition, in the documentation has further information on inheritance.

EXAMPLES¶

To add a column of type varchar to a table:

ALTER TABLE distributors ADD COLUMN address varchar(30);

To drop a column from a table:

ALTER TABLE distributors DROP COLUMN address RESTRICT;

To change the types of two existing columns in one operation:

ALTER TABLE distributors


    ALTER COLUMN address TYPE varchar(80),


    ALTER COLUMN name TYPE varchar(100);

To change an integer column containing UNIX timestamps to timestamp with time zone via a USING clause:

ALTER TABLE foo


    ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone


    USING


        timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';

The same, when the column has a default expression that won't automatically cast to the new data type:

ALTER TABLE foo


    ALTER COLUMN foo_timestamp DROP DEFAULT,


    ALTER COLUMN foo_timestamp TYPE timestamp with time zone


    USING


        timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',


    ALTER COLUMN foo_timestamp SET DEFAULT now();

To rename an existing column:

ALTER TABLE distributors RENAME COLUMN address TO city;

To rename an existing table:

ALTER TABLE distributors RENAME TO suppliers;

To rename an existing constraint:

ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;

To add a not-null constraint to a column:

ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;

To remove a not-null constraint from a column:

ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;

To add a check constraint to a table and all its children:

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);

To add a check constraint only to a table and not to its children:

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;

(The check constraint will not be inherited by future children, either.)

To remove a check constraint from a table and all its children:

ALTER TABLE distributors DROP CONSTRAINT zipchk;

To remove a check constraint from one table only:

ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;

(The check constraint remains in place for any child tables.)

To add a foreign key constraint to a table:

ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);

To add a (multicolumn) unique constraint to a table:

ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);

To add an automatically named primary key constraint to a table, noting that a table can only ever have one primary key:

ALTER TABLE distributors ADD PRIMARY KEY (dist_id);

To move a table to a different tablespace:

ALTER TABLE distributors SET TABLESPACE fasttablespace;

To move a table to a different schema:

ALTER TABLE myschema.distributors SET SCHEMA yourschema;

To recreate a primary key constraint, without blocking updates while the index is rebuilt:

CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,


    ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;

COMPATIBILITY¶

The forms ADD (without USING INDEX), DROP, SET DEFAULT, and SET DATA TYPE (without USING) conform with the SQL standard. The other forms are PostgreSQL extensions of the SQL standard. Also, the ability to specify more than one manipulation in a single ALTER TABLE command is an extension.

ALTER TABLE DROP COLUMN can be used to drop the only column of a table, leaving a zero-column table. This is an extension of SQL, which disallows zero-column tables.

SEE ALSO¶

CREATE TABLE (CREATE_TABLE(7))