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
ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]


    SET TABLESPACE new_tablespace [ NOWAIT ]
ALTER TABLE [ IF EXISTS ] name


    ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }
ALTER TABLE [ IF EXISTS ] name


    DETACH PARTITION partition_name [ CONCURRENTLY | FINALIZE ]
where action is one of:


    ADD [ COLUMN ] [ IF NOT EXISTS ] 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 DROP EXPRESSION [ IF EXISTS ]


    ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]


    ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...]


    ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]


    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 }


    ALTER [ COLUMN ] column_name SET COMPRESSION compression_method


    ADD table_constraint [ NOT VALID ]


    ADD table_constraint_using_index


    ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]


    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


    DISABLE ROW LEVEL SECURITY


    ENABLE ROW LEVEL SECURITY


    FORCE ROW LEVEL SECURITY


    NO FORCE ROW LEVEL SECURITY


    CLUSTER ON index_name


    SET WITHOUT CLUSTER


    SET WITHOUT OIDS


    SET ACCESS METHOD new_access_method


    SET TABLESPACE new_tablespace


    SET { LOGGED | UNLOGGED }


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


    RESET ( storage_parameter [, ... ] )


    INHERIT parent_table


    NO INHERIT parent_table


    OF type_name


    NOT OF


    OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }


    REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }
and partition_bound_spec is:
IN ( partition_bound_expr [, ...] ) |
FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )


  TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )
and column_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL |


  NULL |


  CHECK ( expression ) [ NO INHERIT ] |


  DEFAULT default_expr |


  GENERATED ALWAYS AS ( generation_expr ) STORED |


  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |


  UNIQUE [ NULLS [ NOT ] DISTINCT ] index_parameters |


  PRIMARY KEY index_parameters |


  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]


    [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
and table_constraint is:
[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |


  UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters |


  PRIMARY KEY ( column_name [, ... ] ) index_parameters |


  EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |


  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]


    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
and table_constraint_using_index is:


    [ CONSTRAINT constraint_name ]


    { UNIQUE | PRIMARY KEY } USING INDEX index_name


    [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]
exclude_element in an EXCLUDE constraint is:
{ column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
referential_action in a FOREIGN KEY/REFERENCES constraint is:
{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( column_name [, ... ] ) ] | SET DEFAULT [ ( column_name [, ... ] ) ] }

DESCRIPTION¶

ALTER TABLE changes the definition of an existing table. There are several subforms described below. Note that the lock level required may differ for each subform. An ACCESS EXCLUSIVE lock is acquired unless explicitly noted. When multiple subcommands are given, the lock acquired will be the strictest one required by any subcommand.

ADD COLUMN [ IF NOT EXISTS ]

DROP COLUMN [ IF EXISTS ]

SET DATA TYPE

SET/DROP DEFAULT

SET/DROP NOT NULL

DROP EXPRESSION [ IF EXISTS ]

ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
SET GENERATED { ALWAYS | BY DEFAULT }
DROP IDENTITY [ IF EXISTS ]

SET sequence_option
RESTART

SET STATISTICS

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

SET STORAGE

SET COMPRESSION compression_method

ADD table_constraint [ NOT VALID ]

ADD table_constraint_using_index

ALTER CONSTRAINT

VALIDATE CONSTRAINT

DROP CONSTRAINT [ IF EXISTS ]

DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER

DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE

DISABLE/ENABLE ROW LEVEL SECURITY

NO FORCE/FORCE ROW LEVEL SECURITY

CLUSTER ON

SET WITHOUT CLUSTER

SET WITHOUT OIDS

SET ACCESS METHOD

SET TABLESPACE

SET { LOGGED | UNLOGGED }

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

RESET ( storage_parameter [, ... ] )

INHERIT parent_table

NO INHERIT parent_table

OF type_name

NOT OF

OWNER TO

REPLICA IDENTITY

RENAME

SET SCHEMA

ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }

DETACH PARTITION partition_name [ CONCURRENTLY | FINALIZE ]

All the forms of ALTER TABLE that act on a single table, except RENAME, SET SCHEMA, ATTACH PARTITION, and DETACH PARTITION can be combined into a list of multiple alterations to be applied together. 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 or tablespace of a table, you must also have CREATE privilege on the new schema or tablespace. To add the table as a new child of a parent table, you must own the parent table as well. Also, to attach a table as a new partition of the table, you must own the table being attached. 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¶

IF EXISTS

name

column_name

new_column_name

new_name

data_type

table_constraint

constraint_name

CASCADE

RESTRICT

trigger_name

ALL

USER

index_name

storage_parameter

value

parent_table

new_owner

new_access_method

new_tablespace

new_schema

partition_name

partition_bound_spec

NOTES¶

The key word COLUMN is noise and can be omitted.

When a column is added with ADD COLUMN and a non-volatile DEFAULT is specified, the default is evaluated at the time of the statement and the result stored in the table's metadata. That value will be used for the column for all existing rows. If no DEFAULT is specified, NULL is used. In neither case is a rewrite of the table required.

Adding a column with a volatile DEFAULT or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception, when changing the type of an existing column, 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. However, indexes must always be rebuilt unless the system can verify that the new index would be logically equivalent to the existing one. For example, if the collation for a column has been changed, an index rebuild is always required because the new sort order might be different. However, in the absence of a collation change, a column can be changed from text to varchar (or vice versa) without rebuilding the indexes because these data types sort identically. 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.

Similarly, when attaching a new partition it may be scanned to verify that existing rows meet the partition constraint.

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.

Scanning a large table to verify a new foreign key or check constraint can take a long time, and other updates to the table are locked out until the ALTER TABLE ADD CONSTRAINT command is committed. The main purpose of the NOT VALID constraint option is to reduce the impact of adding a constraint on concurrent updates. With NOT VALID, the ADD CONSTRAINT command does not scan the table and can be committed immediately. After that, a VALIDATE CONSTRAINT command can be issued to verify that existing rows satisfy the constraint. The validation step does not need to lock out concurrent updates, since it knows that other transactions will be enforcing the constraint for rows that they insert or update; only pre-existing rows need to be checked. Hence, validation acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered. (If the constraint is a foreign key then a ROW SHARE lock is also required on the table referenced by the constraint.) In addition to improving concurrency, it can be useful to use NOT VALID and VALIDATE CONSTRAINT in cases where the table is known to contain pre-existing violations. Once the constraint is in place, no new violations can be inserted, and the existing problems can be corrected at leisure until VALIDATE CONSTRAINT finally succeeds.

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.

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.6 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 in the parent table without doing the same to the descendants. This ensures that the descendants always have columns matching the parent. Similarly, a CHECK constraint cannot be renamed in the parent without also renaming it in all descendants, so that CHECK constraints also match between the parent and its descendants. (That restriction does not apply to index-based constraints, however.) Also, because selecting from the parent also selects from its descendants, a constraint on the parent cannot be marked valid unless it is also marked valid for those descendants. In all of these cases, ALTER TABLE ONLY will be rejected.

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. A nonrecursive DROP COLUMN command will fail for a partitioned table, because all partitions of a table must have the same columns as the partitioning root.

The actions for identity columns (ADD GENERATED, SET etc., DROP IDENTITY), as well as the actions CLUSTER, OWNER, and TABLESPACE never recurse to descendant tables; that is, they always act as though ONLY were specified. Actions affecting trigger states recurse to partitions of partitioned tables (unless ONLY is specified), but never to traditional-inheritance descendants. 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 has further information on inheritance.

EXAMPLES¶

To add a column of type varchar to a table:

ALTER TABLE distributors ADD COLUMN address varchar(30);

That will cause all existing rows in the table to be filled with null values for the new column.

To add a column with a non-null default:

ALTER TABLE measurements


  ADD COLUMN mtime timestamp with time zone DEFAULT now();

Existing rows will be filled with the current time as the value of the new column, and then new rows will receive the time of their insertion.

To add a column and fill it with a value different from the default to be used later:

ALTER TABLE transactions


  ADD COLUMN status varchar(30) DEFAULT 'old',


  ALTER COLUMN status SET default 'current';

Existing rows will be filled with old, but then the default for subsequent commands will be current. The effects are the same as if the two sub-commands had been issued in separate ALTER TABLE commands.

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 foreign key constraint to a table with the least impact on other work:

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

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;

To attach a partition to a range-partitioned table:

ALTER TABLE measurement


    ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');

To attach a partition to a list-partitioned table:

ALTER TABLE cities


    ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');

To attach a partition to a hash-partitioned table:

ALTER TABLE orders


    ATTACH PARTITION orders_p4 FOR VALUES WITH (MODULUS 4, REMAINDER 3);

To attach a default partition to a partitioned table:

ALTER TABLE cities


    ATTACH PARTITION cities_partdef DEFAULT;

To detach a partition from a partitioned table:

ALTER TABLE measurement


    DETACH PARTITION measurement_y2015m12;

COMPATIBILITY¶

The forms ADD (without USING INDEX), DROP [COLUMN], DROP IDENTITY, RESTART, SET DEFAULT, SET DATA TYPE (without USING), SET GENERATED, and SET sequence_option 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))