ALTER TABLE
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
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 }
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 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_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 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 ( 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 action ] [ ON UPDATE 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 ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
Description
ALTER TABLE changes the definition of an existing table. There are several sub-forms described below. Note that the lock level required may vary for each sub-form. An ACCESS EXCLUSIVE lock is acquired unless explicitly stated otherwise. When multiple sub-commands are given, the lock acquired will be the strictest one required by any sub-command.
ADD COLUMN [ IF NOT EXISTS ]
This form adds a new column to the table, using the same syntax as CREATE TABLE. If IF NOT EXISTS is specified and a column with this name already exists, no error is raised.
DROP COLUMN [ IF EXISTS ]
This form drops a column from a table. Indexes and table constraints involving the column will be automatically dropped as well. If removing the column would cause a multivariate statistic referencing it to contain data from only a single column, that multivariate statistic will also be removed. If anything outside the table depends on the column (such as foreign key references or views), you will need to use CASCADE. If IF EXISTS is specified and the column does not exist, no error is raised. A notice is issued in this case.
SET DATA TYPE
This form changes the type of a column in a table. Indexes and simple table constraints involving the column will be automatically converted to use the new column type by re-parsing the originally provided expression. The optional COLLATE clause specifies a collation for the new column; if omitted, the collation is the default for the new column type. The optional USING clause specifies how to compute the new column value from the old column value; if omitted, the default conversion is the same as an assignment cast from the old type to the new type. If there is no implicit or assignment cast from the old type to the new type, a USING clause must be provided.
SET/DROP DEFAULT
These forms set or remove the default value for a column (removing is equivalent to setting the default value to NULL). The new default value only applies to subsequent INSERT or UPDATE commands; it does not cause rows already in the table to change.
SET/DROP NOT NULL
These forms change whether a column is marked as allowing or rejecting null values.
SET NOT NULL can only be applied to a column provided that none of the records in the table contain NULL values for that column. Normally, this is checked during a full table scan by ALTER TABLE; however, if a valid CHECK constraint is found that proves no NULLs exist, the table scan is skipped.
If this table is a partition, DROP NOT NULL cannot be executed on a column that is marked NOT NULL in the parent table. To remove the NOT NULL constraint from all partitions, you can execute DROP NOT NULL on the parent table. Even if the parent table does not have a NOT NULL constraint, such a constraint can still be added to a partition. That is, even if the parent table allows null values, a child table can disallow them, but not the other way around.
DROP EXPRESSION [ IF EXISTS ]
This form converts a stored generated column into a regular base column. Existing data in the column will be retained, but future changes will not apply the generation expression.
If DROP EXPRESSION IF EXISTS is specified and the column is not a stored generated column, no error is raised. A notice is issued in this case.
ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
SET GENERATED { ALWAYS | BY DEFAULT }
DROP IDENTITY [ IF EXISTS ]
These forms change whether a column is an identity column, or change the generation attribute of an existing identity column. See CREATE TABLE for details. Like SET DEFAULT, these forms only affect the behavior of subsequent INSERT and UPDATE commands. They do not cause rows already in the table to change. If DROP IDENTITY IF EXISTS is specified and the column is not an identity column, no error is raised. A notice is issued in this case.
SET sequence_option
RESTART
These forms modify the sequence underlying an existing identity column. sequence_option is an option supported by ALTER SEQUENCE, such as INCREMENT BY.
SET STATISTICS
This form sets the per-column statistics collection target for subsequent ANALYZE operations. The target can be set in the range 0 to 10000, or set to -1 to revert to using the system default statistics target (default_statistics_target).
SET ( attribute_option = value [, ... ] )
RESET ( attribute_option [, ... ] )
These forms set or reset per-attribute options. Currently, the defined per-attribute options are n_distinct and n_distinct_inherited, which override the number-of-distinct-values estimate obtained by subsequent ANALYZE operations. n_distinct affects the statistics for the table itself, while n_distinct_inherited affects the statistics collected for the table plus its inheritance children. When set to a positive value, ANALYZE will assume that the column contains exactly the specified number of distinct non-null values. When set to a negative value (which must be greater than or equal to -1), ANALYZE will assume that the number of distinct non-null values is linearly proportional to the size of the table; the exact count is computed by multiplying the estimated table size by the absolute value of the given number. For example, a value of -1 means all values in the column are distinct, while -0.5 means each value appears on average twice. This is helpful when the table size changes over time, because this calculation is only performed during query planning. Specifying a value of 0 reverts to the normal method of estimating the number of distinct values.
SET STORAGE
This form sets the storage mode for a column. This controls whether the column is held inline or in a secondary TOAST table, and whether the data should be compressed. PLAIN must be used for fixed-length, inline, uncompressed values such as integers. MAIN is for inline, compressible data. EXTERNAL is for external, uncompressed data. EXTENDED is for external, compressed data. EXTENDED is the default for most data types that support non-PLAIN storage. Using EXTERNAL will make substring operations on large text and bytea values faster, at the cost of increased storage space. Note that SET STORAGE itself does not change anything in the table; it only sets the strategy to pursue during future table updates.
ADD table_constraint [ NOT VALID ]
This form adds a new constraint to a table using the same constraint syntax as CREATE TABLE, plus the NOT VALID option, which is currently only allowed for foreign key and CHECK constraints.
Typically, this form will cause a table scan to verify that all existing rows in the table satisfy the new constraint. However, if the NOT VALID option is used, this potentially lengthy scan is skipped. The constraint will still be enforced on subsequent inserts and deletes (that is, in the case of a foreign key, the operation will fail if there is no matching row in the referenced table; or the operation will fail if the new row does not match the specified check condition). However, the database will not assume that the constraint holds for all rows in the table until it is validated using the VALIDATE CONSTRAINT option. See the Notes below for more information about using the NOT VALID option.
Although most forms of ADD table_constraint require an ACCESS EXCLUSIVE lock, ADD FOREIGN KEY only requires a SHARE ROW EXCLUSIVE lock. Note that in addition to the lock on the table declaring the constraint, ADD FOREIGN KEY also acquires a SHARE ROW EXCLUSIVE lock on the referenced table. When a unique or primary key constraint is added to a partitioned table, there are additional restrictions; see CREATE TABLE. Furthermore, foreign key constraints on partitioned tables currently cannot be declared as NOT VALID.
ADD table_constraint_using_index
This form adds a new PRIMARY KEY or UNIQUE constraint to a table based on an existing unique index. All columns in the index will be included in the constraint.
The index cannot have expression columns or be a partial index. Also, it must be a B-tree index with the default sort order. These restrictions ensure that the index is equivalent to one that would be created by a regular ADD PRIMARY KEY or ADD UNIQUE command.
If PRIMARY KEY is specified and the index columns are not marked NOT NULL, this command will attempt to execute ALTER COLUMN SET NOT NULL for each such column. This requires a full table scan to verify that the columns do not contain nulls. In all other cases, this is a fast operation.
If a constraint name is provided, the index will be renamed to match the constraint name. Otherwise, the constraint will be named after the index.
After this command is executed, the index is "owned" by the added constraint, just like an index created with a regular ADD PRIMARY KEY or ADD UNIQUE command. In particular, dropping the constraint will cause the index to disappear as well. This form is currently not supported on partitioned tables.
Note: If you need to add a new constraint but do not want to block table updates for a long time, adding the constraint using an existing index can help. To do this, create the index with CREATE INDEX CONCURRENTLY, and then install it as a formal constraint using this syntax. See the examples below.
ALTER CONSTRAINT
This form modifies the attributes of a previously created constraint. Currently, only foreign key constraints can be modified.
VALIDATE CONSTRAINT
This form validates a foreign key or check constraint that was previously created as NOT VALID. It scans the table to ensure that no rows violate the constraint. If the constraint is already marked as valid, nothing happens. (See the Notes below for an explanation of the purpose of this command.)
DROP CONSTRAINT [ IF EXISTS ]
This form drops the specified constraint on a table, along with any index underlying the constraint. If IF EXISTS is specified and the constraint does not exist, no error is raised. A notice is issued in this case.
DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
These forms configure the firing settings for triggers belonging to the table. The system is still aware of disabled triggers, but they will not be executed even when their triggering event occurs. For a deferred trigger, the enabled status is checked when the event occurs rather than when the trigger function is actually executed. You can disable or enable a single trigger by name, all triggers on the table, or user-owned triggers (this option excludes internally generated constraint triggers, such as those used to implement foreign key constraints or deferrable unique and exclusion constraints). Disabling or enabling internally generated constraint triggers requires superuser privileges; use caution because if such triggers are not executed, constraint integrity cannot be guaranteed.
The trigger firing mechanism is also affected by the configuration variable session_replication_role. When the replication role is "origin" (the default) or "local", simply enabled triggers will be fired. Triggers configured as ENABLE REPLICA will only be fired when the session is in "replica" mode. Triggers configured as ENABLE ALWAYS will be fired regardless of the current replication role. The effect of this mechanism is that, in the default configuration, triggers are not fired on replicas. This is useful because if a trigger is used on the source to propagate data between tables, the replication system will also replicate the propagated data, and the trigger should not fire a second time on the replica, as that would cause duplication. However, if a trigger is used for another purpose (such as creating external alerts), setting it to ENABLE ALWAYS may be more appropriate so that it fires on replicas as well.
This command requires a SHARE ROW EXCLUSIVE lock.
DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE
These forms configure the firing settings for rewrite rules belonging to the table. The system is still aware of a disabled rule, but it will not be applied during query rewriting. The semantics are the same as for disabled/enabled triggers. This configuration is ignored for ON SELECT rules; such rules are always applied regardless of the current session's replication role, to keep views working properly.
The rule firing mechanism is also affected by the configuration variable session_replication_role, similar to triggers described above.
DISABLE/ENABLE ROW LEVEL SECURITY
These forms control the application of row security policies belonging to the table. If enabled and no policies exist on the table, a default-deny policy will be applied. Note that policies can exist on a table even when row-level security is disabled.
In this case, the policies will not be applied and will be ignored. See also CREATE POLICY.
NO FORCE/FORCE ROW LEVEL SECURITY
These forms control the application of row security policies on the table when the user is the table owner. If enabled, row-level security policies will be applied when the user is the table owner. If disabled (the default), row-level security will not be applied when the user is the table owner. See also CREATE POLICY.
CLUSTER ON — This form selects the default index for future CLUSTER operations. It does not actually cluster the table. Changing the cluster option requires a SHARE UPDATE EXCLUSIVE lock.
SET WITHOUT CLUSTER
This form removes the most recently used CLUSTER index specification from the table. This affects future cluster operations that do not specify an index.
Changing the cluster option requires a SHARE UPDATE EXCLUSIVE lock.
SET WITHOUT OIDS
Backward-compatible syntax for removing the oid system column. Since the oid system column can no longer be added, this has no actual effect.
SET TABLESPACE
This form changes the table's tablespace to the specified tablespace and moves the data files associated with the table to the new tablespace. Indexes on the table (if any) are not moved, but they can be moved individually with additional SET TABLESPACE commands. When applied to a partitioned table, nothing is moved, but any partitions created subsequently with CREATE TABLE PARTITION OF will use that tablespace unless overridden by a TABLESPACE clause.
All tables in a tablespace in the current database can be moved using the ALL IN TABLESPACE form, which will first lock all tables to be moved and then move them one by one. This form also supports OWNED BY, which will only move tables owned by the specified roles. If the NOWAIT option is specified, the command will fail if it cannot acquire all required locks immediately. Note that this command does not move system catalogs; to move system catalogs, use ALTER DATABASE or explicit ALTER TABLE calls. For this form, information_schema relations are not considered part of the system catalog, so they will be moved. See also CREATE TABLESPACE.
SET { LOGGED | UNLOGGED }
This form changes the table from unlogged to logged or vice versa (see UNLOGGED).
It cannot be applied to a temporary table.
SET ( storage_parameter [= value] [, ... ] )
This form changes one or more storage parameters for the table. See Storage Parameters in CREATE TABLE for available parameters. Note that this command will not immediately modify table contents; depending on the parameter, a table rewrite may be needed to achieve the desired result. You can use VACUUM FULL, CLUSTER, or one of the forms of ALTER TABLE to force a table rewrite. For planner-related parameters, changes take effect the next time the table is locked, so currently executing queries are not affected.
For fillfactor, toast, and autovacuum storage parameters, a SHARE UPDATE EXCLUSIVE lock is acquired, as with the planner parameter parallel_workers.
RESET ( storage_parameter [, ... ] )
This form resets one or more storage parameters to their default values. As with SET, a table rewrite may be needed to update the entire table.
INHERIT parent_table
This form adds the target table as a new child of the specified parent table. Subsequently, queries against the parent will include records from the target table. To be added as a child, the target table must already contain exactly the same columns as the parent (it may have additional columns). These columns must have matching data types, and if they have NOT NULL constraints in the parent table, they must also have NOT NULL constraints in the child table. Child table constraints must also match all CHECK constraints of the parent table, except those marked as non-inheritable (i.e., created with ALTER TABLE ... ADD CONSTRAINT ... NO INHERIT) in the parent, which are ignored. All matching child table constraints must not be marked as non-inheritable. Currently, UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints are not considered, but this may change in the future.
NO INHERIT parent_table
This form removes the target table from the children list of the specified parent table. Queries against the parent table will no longer include records from the target table.
OF type_name
This form links the table to a composite type, as if created by CREATE TABLE OF. The table's column name and type list must exactly match the composite type. The table must not inherit from any other table. These restrictions ensure that CREATE TABLE OF would allow an equivalent table definition.
NOT OF
This form dissociates a typed table from its type.
OWNER TO
This form changes the owner of the table, sequence, view, materialized view, or foreign table to the specified user.
REPLICA IDENTITY
This form changes the information written to the write-ahead log to identify rows that are updated or deleted. This option has no effect unless logical replication is used. DEFAULT (the default for non-system tables) records the old values of the primary key columns (if any). USING INDEX records the old values of the columns covered by the specified index, which must be a unique, non-partial, non-deferrable index that includes only columns marked NOT NULL. FULL records the old values of all columns in the row. NOTHING records no information about the old row (this is the default for system tables). In all cases, old values are not recorded unless at least one column to be recorded differs between the old and new row versions.
RENAME
The RENAME form changes the name of a table (or an index, sequence, view, materialized view, or foreign table), the name of a column in the table, or the name of a constraint on the table. When renaming a constraint that has an underlying index, the index will also be renamed. It has no effect on stored data.
SET SCHEMA
This form moves the table to another schema. Associated indexes, constraints, and sequences owned by the table's columns will also be moved.
ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }
This form attaches an existing table (which may itself be partitioned) as a partition of the target table. The table can be attached as a partition for specific values using FOR VALUES, or as a default partition using DEFAULT. For each index on the target table, a corresponding index will be created on the attached table; if an equivalent index already exists, it will be attached to the target table's index, as if ALTER INDEX ATTACH PARTITION had been executed.
Note that if the existing table is a foreign table, it is currently not allowed to attach it as a partition of the target table if there are UNIQUE indexes on the target table. (See CREATE FOREIGN TABLE.) For each user-defined row-level trigger that exists on the target table, a corresponding trigger will be created on the attached table.
A partition using FOR VALUES uses the same syntax for partition_bound_spec as in CREATE TABLE. The partition bound specification must correspond to the target table's partitioning strategy and partition key. The table to be attached must have exactly the same columns as the target table, no extra columns, and the column types must match. In addition, it must have all NOT NULL and CHECK constraints from the target table. FOREIGN KEY constraints are not currently considered. UNIQUE and PRIMARY KEY constraints from the parent table will be created on the partition (if they do not already exist). If any CHECK constraint on the table to be attached is marked as NO INHERIT, the command will fail; such constraints must be rebuilt without the NO INHERIT clause.
If the new partition is a regular table, a full table scan is performed to check that existing rows do not violate the partition constraint. This scan can be avoided by adding a valid CHECK constraint to the table before running this command, one that allows only rows satisfying the desired partition constraint. The CHECK constraint can resolve the verification of the partition constraint without scanning the table. However, this approach does not work if any partition key is an expression and the partition does not accept NULL values. If attaching a list partition that does not accept NULL values, you should also add a NOT NULL constraint to the partition key column unless it is an expression. If the new partition is a foreign table, there is no need to verify that all rows in the foreign table obey the partition constraint (see the discussion of constraints on foreign tables in CREATE FOREIGN TABLE).
When a table has a default partition, defining a new partition changes the partition constraint of the default partition. The default partition must not contain any rows that need to be moved to the new partition, and it will be scanned to verify that no such rows exist. If a suitable CHECK constraint exists, this scan (like the scan of the new partition) can be avoided. As with the new partition scan, this scan is always skipped when the default partition is a foreign table. Attaching a partition on the parent table acquires a SHARE UPDATE EXCLUSIVE lock, in addition to ACCESS EXCLUSIVE locks on the table being attached and the default partition (if any).
DETACH PARTITION partition_name
This form detaches the specified partition of the target table. The detached partition continues to exist as an independent table, but no longer has any association with the table it was previously attached to. Any indexes that were attached to the target table's indexes will also be detached. Any tables that were created as copies of those in the target table will also be dropped.
Except for RENAME, SET SCHEMA, ATTACH PARTITION, and DETACH PARTITION, all forms of ALTER TABLE operate on a single table; the aforementioned forms can be combined into a list of multiple modifications to be applied together. For example, you can add multiple columns and/or change the types of multiple columns in a single command. This is particularly useful for large tables, since only one pass over the table is needed. To use ALTER TABLE, you must own the table. To change the schema or tablespace of a table, you must also have the CREATE privilege on the new schema or tablespace. To add a table as a new child of a parent table, you must also own the parent table. Furthermore, to attach a table as a new partition of another table, you must own the table being attached. To change the owner, you must also be a direct or indirect member of the new owning role, and that role must have the CREATE privilege on the table's schema (these restrictions enforce that changing the owner cannot do anything that could not be accomplished by dropping and recreating the table. However, a superuser can always change the ownership of any table.). To add a column, change the type of a column, or use the OF clause, you must also have the USAGE privilege on the data type.
Parameters
IF EXISTS
Do not throw an error if the table does not exist. A notice is issued in this case.
name
The name of an existing table to be altered (can be schema-qualified). If ONLY is specified before the table name, only that table is altered. If ONLY is not specified, the table and all its descendant tables (if any) are altered. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are included.
column_name
The name of a new or existing column.
new_column_name
The new name for an existing column.
new_name
The new name of the table.
data_type
The data type of a new column or the new data type of an existing column.
table_constraint
The new table constraint for the table.
constraint_name
The name of a new or existing constraint.
CASCADE
Automatically drop objects that depend on the dropped column or constraint (such as views referencing the column), and in turn drop objects that depend on those objects.
RESTRICT
Refuse to drop the column or constraint if any objects depend on it. This is the default behavior.
trigger_name
The name of a trigger to be disabled or enabled.
ALL
Disable or enable all triggers belonging to the table (if any triggers are internally generated constraint triggers, superuser privileges are required, such as those used to implement foreign key constraints or deferrable consistency and exclusion constraints).
USER
Disable or enable all triggers belonging to the table, excluding internally generated constraint triggers (such as those used to implement foreign key constraints or deferrable consistency and exclusion constraints).
index_name
The name of an existing index.
storage_parameter
The name of a table storage parameter.
value
The new value for a table storage parameter. Depending on the parameter, this may be a number or a word.
parent_table
The parent table to be associated or disassociated with this table.
new_owner
The user name of the new owner of the table.
new_tablespace
The name of the tablespace to which the table will be moved.
new_schema
The name of the schema to which the table will be moved.
partition_name
The name of the table to be attached as a new partition to or detached from this table.
partition_bound_spec
The partition bound specification for the new partition. See CREATE TABLE for details on the same syntax.
Notes
The keyword COLUMN is noise and can be omitted.
When using ADD COLUMN to add a column with a non-volatile DEFAULT specified, the default value is computed at statement execution time, and the result is stored in the table's metadata. This value will be used for that column in all existing rows. If no DEFAULT is specified, NULL is used. In neither case is a table rewrite required. Adding a column with a non-volatile DEFAULT clause or changing the type of an existing column requires rewriting the entire table and its indexes. 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 binary-coercible to the new type or is an unconstrained domain on the new type, a table rewrite is not needed. However, any indexes on the affected columns must still be rebuilt. For a large table, table and/or index rebuilding can take a considerable amount of time and temporarily require approximately twice the disk space.
Adding a CHECK or NOT NULL constraint requires scanning the table to verify that existing rows satisfy the constraint, but does not require a table rewrite.
Similarly, when attaching a new partition, it needs to be scanned to verify that existing rows satisfy 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 thus be consolidated into one.
Scanning a large table to verify a new foreign key or check constraint can take a long time, and other updates to the table will be locked until the ALTER TABLE ADD CONSTRAINT command is committed. The primary 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 commit immediately. Afterwards, a VALIDATE CONSTRAINT command can be issued to verify that existing rows satisfy the constraint. The validation step does not need to lock concurrent updates, because it knows that other transactions will enforce the constraint for rows they insert or update; only pre-existing rows need to be checked. Therefore, validation acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered. (If the constraint is a foreign key, a ROW SHARE lock on the referenced table is also required.) In addition to improving concurrency, using NOT VALID and VALIDATE CONSTRAINT can also be useful when it is known that the table contains pre-existing violations. Once the constraint is in place, no new violations can be inserted, and existing issues can be corrected at leisure until VALIDATE CONSTRAINT is eventually completed. 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 that column. Therefore, dropping a column is fast, but it does not immediately reduce the disk space occupied by the table, because the space occupied by the dropped column has not been reclaimed. As existing columns are updated, the space will be gradually reclaimed. To force immediate reclamation of the space occupied by dropped columns, you can execute a form of ALTER TABLE that causes a full table rewrite. This form will reconstruct each row, replacing the dropped column with a null value.
The rewrite forms of ALTER TABLE are not MVCC-safe. After a table rewrite, the table will appear empty to concurrent transactions that are using a snapshot taken before the rewrite occurred.
The USING option of SET DATA TYPE can actually specify any expression involving the old values of the column. That is, it can reference not only the column being converted, but also other columns. This allows very general transformations to be accomplished using the SET DATA TYPE syntax. Due to this flexibility, the USING expression is not suitable for the column's default value (if any), as the result may not be the constant expression required for a default value. This means that when there is no implicit or assignment cast from the old type to the new type, SET DATA TYPE may not be able to convert the default value even if a USING clause is provided. In this case, use DROP DEFAULT to remove the default value, execute ALTER TYPE, and then use SET DEFAULT to add an appropriate new default value. Similar considerations apply to indexes and constraints involving the column.
If a table has any descendant tables, it is not permitted to add columns, rename columns, or change column types in the parent table without performing the same operation on the descendant tables. This ensures that descendants always have columns matching the parent. Similarly, a CHECK constraint cannot be renamed in the parent table without renaming it on all descendant tables, so that CHECK constraints remain matched between the parent and its descendants (however, this restriction does not apply to index-based constraints). Furthermore, because selecting from the parent table also selects from its descendants, a constraint on the parent table cannot be marked as valid unless it is also marked as valid on those descendants. In all these cases, ALTER TABLE ONLY will be rejected.
A recursive DROP COLUMN operation will only remove a column from a descendant table if that column is not inherited from any other parent table and does not have an independent definition of that column. A non-recursive DROP COLUMN (i.e., ALTER TABLE ONLY ... DROP COLUMN) will not remove any descendant columns, but will mark them as independently defined columns. For a partitioned table, a non-recursive DROP COLUMN command will fail because all partitions of a table must have the same columns as the partition root.
Actions on identity columns (ADD GENERATED, SET, etc.) and actions TRIGGER, CLUSTER, OWNER, and TABLESPACE are not recursed to descendant tables; that is, they are always executed as if ONLY were specified. The action of adding constraints is only recursed for CHECK constraints that are not marked as NO INHERIT.
It is not permitted to alter any part of a system catalog table.
For further description of available parameters, see CREATE TABLE.
Examples
# To add a column of type varchar to a table:
ALTER TABLE distributors ADD COLUMN address varchar(30);
# This 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 value:
ALTER TABLE measurements
ADD COLUMN mtime timestamp with time zone DEFAULT now();
# Existing rows will be filled with the current time as the new column value, and then new rows will use their insertion time.
# To add a column and fill it with a value different from the default:
ALTER TABLE transactions
ADD COLUMN status varchar(30) DEFAULT 'old',
ALTER COLUMN status SET default 'current';
# Existing rows will be filled with 'old', but the default value for subsequent commands will be 'current'. The effect is the same as issuing two sub-commands 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 a 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';
# Similarly, when the column has a default value expression that cannot be 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 to a table only (not to its children):
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5)
NO INHERIT;
# (The check constraint will also not be inherited by future children.)
# To remove a check constraint from a table and all its children:
ALTER TABLE distributors DROP CONSTRAINT zipchk;
# To remove a check constraint from only one table:
ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
# (The check constraint remains in place on the 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 minimal 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 (note that a table can only 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 rebuild a primary key constraint without blocking updates during index rebuilding:
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;