# man > ALTER_TABLE(7) --- type: CommandReference command: ALTER TABLE mode: man section: '7' source: man-pages --- ## Quick Reference - `ALTER TABLE distributors ADD COLUMN address varchar(30);` — Add a column - `ALTER TABLE distributors DROP COLUMN address RESTRICT;` — Drop a column - `ALTER TABLE distributors RENAME COLUMN address TO city;` — Rename a column - `ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;` — Add a NOT NULL constraint - `ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);` — Add a CHECK constraint - `ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;` — Add a foreign key without immediate validation - `ALTER TABLE measurement ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');` — Attach a partition - `ALTER TABLE measurement DETACH PARTITION measurement_y2015m12;` — Detach a partition ## Name `ALTER TABLE` — change the definition of a table ## Synopsis text 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 | 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 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 } ## Options ### Column Operations - **ADD COLUMN** — Adds a new column, with optional `IF NOT EXISTS`. Takes the same syntax as `CREATE TABLE`. - **DROP COLUMN** — Drops a column, along with related indexes and constraints. Requires `CASCADE` if external objects depend on it. `IF EXISTS` avoids errors. - **SET DATA TYPE** — Changes the data type of a column. The optional `USING` expression converts existing values; `COLLATE` sets collation. A table rewrite may occur. - **SET DEFAULT / DROP DEFAULT** — Sets or removes the default value. Affects only future inserts/updates. - **SET NOT NULL / DROP NOT NULL** — Changes nullability. `SET NOT NULL` scans the table unless a valid CHECK constraint proves no nulls exist. Partitions restrict `DROP NOT NULL` if parent column is NOT NULL. - **DROP EXPRESSION** — Converts a stored generated column to a normal base column, keeping existing data. - **ADD GENERATED / SET GENERATED / DROP IDENTITY** — Modify identity columns. `DROP IDENTITY` removes the identity property. `IF EXISTS` avoids errors. - **SET sequence_option / RESTART** — Alter underlying sequence of an identity column (e.g., `INCREMENT BY`). - **SET STATISTICS** — Sets per-column statistics target (0–10000; -1 reverts to default). Acquires `SHARE UPDATE EXCLUSIVE` lock. - **SET (attribute_option) / RESET** — Sets or resets per-attribute options (`n_distinct`, `n_distinct_inherited`) for the planner. Lock: `SHARE UPDATE EXCLUSIVE`. - **SET STORAGE** — Chooses storage strategy: `PLAIN`, `MAIN`, `EXTERNAL`, `EXTENDED`. Does not rewrite existing data. - **SET COMPRESSION** — Sets compression method (`pglz`, `lz4`, or `default`) for future inserts. ### Constraint Operations - **ADD table_constraint [ NOT VALID ]** — Adds a CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY, or EXCLUDE constraint. `NOT VALID` skips the initial verification scan (allowed for CHECK and FK). FK addition acquires `SHARE ROW EXCLUSIVE` lock on both tables. - **ADD table_constraint USING INDEX** — Adds PRIMARY KEY or UNIQUE using an existing b-tree index (no expression columns, not partial, default sort). The index becomes owned by the constraint. - **ALTER CONSTRAINT** — Alters attributes of an existing foreign key constraint (deferrability, initial state). - **VALIDATE CONSTRAINT** — Validates a previously `NOT VALID` constraint by scanning existing rows. Acquires `SHARE UPDATE EXCLUSIVE` lock. - **DROP CONSTRAINT** — Drops a constraint and its underlying index. `IF EXISTS` avoids errors. `RESTRICT`/`CASCADE` control dependent objects. ### Trigger, Rule, and Security Operations - **ENABLE / DISABLE TRIGGER** — Controls firing of triggers. `USER` excludes internally generated constraint triggers. `REPLICA`/`ALWAYS` modes control replication role sensitivity. Acquires `SHARE ROW EXCLUSIVE` lock. - **ENABLE / DISABLE RULE** — Configures rewrite rules. `ON SELECT` rules are always applied to preserve views. - **ENABLE / DISABLE ROW LEVEL SECURITY** — Toggles row security policies; if enabled and no policies exist, a default‑deny policy applies. - **FORCE / NO FORCE ROW LEVEL SECURITY** — Controls whether row security applies to table owners. ### Storage, Tablespace, and Unlogged - **SET TABLESPACE** — Moves the table’s data files to a new tablespace. Indexes are not moved. `ALL IN TABLESPACE` with optional `OWNED BY` and `NOWAIT` moves all matching tables. - **SET { LOGGED | UNLOGGED }** — Changes logging mode. Cannot be applied to temporary tables. - **SET (storage_parameter) / RESET** — Adjusts storage parameters (e.g., `fillfactor`, `autovacuum`). Some parameters require a table rewrite. - **CLUSTER ON / SET WITHOUT CLUSTER** — Sets/removes the default index for future `CLUSTER` commands. - **SET WITHOUT OIDS** — Historical syntax; has no effect. ### Inheritance and Typed Tables - **INHERIT / NO INHERIT** — Adds/removes the table as a child of a parent table. Child must contain all parent columns with matching types and constraints. - **OF type_name / NOT OF** — Links/unlinks the table to/from a composite type. The table’s column list must exactly match the type. - **OWNER TO** — Changes the table owner. - **REPLICA IDENTITY** — Sets the information written to WAL for logical replication. Values: `DEFAULT` (primary key columns), `USING INDEX`, `FULL`, `NOTHING`. ### Renaming, Schema, and Partitions - **RENAME** — Renames a table, column, or constraint. Underlying indexes of renamed constraints are also renamed. - **SET SCHEMA** — Moves the table and its dependent indexes, constraints, and owned sequences to another schema. - **ATTACH PARTITION** — Attaches an existing table as a partition of the target table. Requires matching columns and constraints. A full scan verifies existing rows unless a suitable CHECK constraint exists. `SHARE UPDATE EXCLUSIVE` lock on parent. - **DETACH PARTITION** — Detaches a partition. With `CONCURRENTLY`, uses a non‑blocking two‑transaction process. `FINALIZE` completes an interrupted concurrent detach. Cannot run in a transaction block when concurrent. ## Parameters - `IF EXISTS` — Do not raise an error if the table is missing; a notice is issued. - `name` — Table name (schema-qualified). `ONLY` restricts to the named table; `*` includes descendants. - `column_name` — Name of an existing or new column. - `new_column_name`, `new_name` — New identifier for the column or table. - `data_type` — Data type for the new column or type conversion. - `table_constraint` — New constraint definition. - `constraint_name` — Name for a new or existing constraint. - `CASCADE` — Drop objects that depend on the removed column/constraint. - `RESTRICT` — Refuse to drop if dependent objects exist (default). - `trigger_name` — Single trigger to enable/disable. - `ALL` — All triggers (requires superuser for internally generated ones). - `USER` — All triggers except internally generated constraint triggers. - `index_name` — Existing index name. - `storage_parameter` — Name of a storage parameter. - `value` — Value for a storage parameter. - `parent_table` — Parent table for inheritance. - `new_owner` — New owner (must have CREATE on the schema). - `new_tablespace` — Target tablespace. - `new_schema` — Target schema. - `partition_name` — Partition table name. - `partition_bound_spec` — Partition bound (see `CREATE TABLE` for details). ## Examples Add a column: sql ALTER TABLE distributors ADD COLUMN address varchar(30); Add a column with a non‑null default (existing rows get the default value): sql ALTER TABLE measurements ADD COLUMN mtime timestamp with time zone DEFAULT now(); Add a column and later change the default without rewriting: sql ALTER TABLE transactions ADD COLUMN status varchar(30) DEFAULT 'old', ALTER COLUMN status SET DEFAULT 'current'; Drop a column: sql ALTER TABLE distributors DROP COLUMN address RESTRICT; Change types of two columns in one pass: sql ALTER TABLE distributors ALTER COLUMN address TYPE varchar(80), ALTER COLUMN name TYPE varchar(100); Convert an integer Unix timestamp using `USING`: sql 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'; Rename a column, table, or constraint: sql ALTER TABLE distributors RENAME COLUMN address TO city; ALTER TABLE distributors RENAME TO suppliers; ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check; Add / remove a NOT NULL constraint: sql ALTER TABLE distributors ALTER COLUMN street SET NOT NULL; ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL; Add a CHECK constraint (without inheritance): sql ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT; Add a foreign key with minimal locking (skip initial scan, validate later): sql ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID; ALTER TABLE distributors VALIDATE CONSTRAINT distfk; Recreate a primary key without long‑running locks using an index built concurrently: sql 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; Attach and detach partitions: sql ALTER TABLE measurement ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); ALTER TABLE measurement DETACH PARTITION measurement_y2015m12; ## See Also - [CREATE TABLE](http://localhost/phpMan.php/man/TABLE/7/markdown)