ALTER TABLE

A DDL command to change the definition of a table

ALTER TABLE is a DDL command to change the definition of a table

ALTER TABLE has been present in every version of PostgreSQL.

Change history

Note: this list may be incomplete, particularly for versions prior to PostgreSQL 9.0.

  • PostgreSQL 15
    • ALTER TABLE ... SET ACCESS METHOD syntax added (commit b0483263)
  • PostgreSQL 14
    • ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY syntax added (commit 71f4c8c6)
    • ALTER TABLE ... ALTER [ COLUMN ] ... SET COMPRESSION syntax added (commit bbe0a81d)
  • PostgreSQL 13
  • PostgreSQL 12
    • ALTER TABLE ... ATTACH PARTITION is now performed with reduced locking requirements (commit 898e5e32)
    • ALTER TABLE ... SET DATA TYPE avoids a table rewrite when changing between timestamp and timestamptz when the session time zone is UTC (commit 3c592630)
    • ALTER TABLE ... SET NOT NULL can avoid unnecessary table scans (commit bbb96c37)
    • ALTER TABLE can now be executed cleanly on system catalogue tables (commit 590a8702)
  • PostgreSQL 11
    • ALTER TABLE ... ADD COLUMN with a non-null constant default no longer requires a table rewrite (initial commit 16828d5c)
  • PostgreSQL 10
    • ALTER TABLE ... ADD PRIMARY KEY propagates any NOT NULL change to inheritance child tables (commit c30f1770)
  • PostgreSQL 9.6
    • ALTER TABLE ... ADD COLUMN ... IF NOT EXISTS added (commit 2cd40adb)
    • ALTER TABLE ... SET (fillfactor = ...) can be performed at a reduced lock level (commit fcb4bfdd)
    • ALTER TABLE ... SET (autovacuum* = ...) can be performed at a reduced lock level (commit 47167b79)
  • PostgreSQL 9.5
    • ALTER TABLE ... (ENABLE|DISABLE) ROW SECURITY added (commit 491c029d)
    • ALTER TABLE ... SET (LOGGED|UNLOGGED) added (commit f41872d0)
    • Support for ALTER TABLE IF EXISTS added to ALTER TABLE ... RENAME CONSTRAINT (commit 1d8198bb)
    • ALTER TABLE will now execute the table_rewrite event trigger (commit 618c9430)
  • PostgreSQL 9.4
    • ALTER TABLE ALL IN TABLESPACE ... added (initial commit fbe19ee3; commit d9b2bc45 established this syntax)
    • ALTER TABLE ... REPLICA IDENTITY ...  syntax added (initial commit 07cacba9)
    • The following ALTER TABLE commands modified to execute at lower lock levels (commit e5550d5f):
      • ALTER COLUMN SET STATISTICS
      • ALTER COLUMN SET (...)
      • ALTER COLUMN RESET (...)
      • CLUSTER ON
      • SET WITHOUT CLUSTER
      • VALIDATE CONSTRAINT
  • PostgreSQL 9.1
    • NOT VALID option added to ALTER TABLE ... ADD constraint (commit 722bf701)
    • ALTER TABLE VALIDATE constraint syntax added (commit 722bf701)
    • ALTER TABLE name {OF type | NOT OF type} syntax added (commit 68739ba8)
  • PostgreSQL 8.4
    • ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE syntax added (commit 1471e384)
  • PostgreSQL 8.2
    • ALTER TABLE ... [ NO ] INHERIT syntax added (commit 8c092781)
  • PostgreSQL 8.0
    • ALTER TABLE ... ADD COLUMN with defaults and NOT NULL constraints (commit 077db40f)
    • ALTER TABLE ... SET TABLESPACE ... syntax added (commit af4de814)
    • ALTER TABLE ... SET WITHOUT CLUSTER syntax added (commit 1cdc5872)
    • column types can be altered (commit 077db40f)
    • multiple ALTER actions in a single ALTER TABLE command supported (commit 077db40f)
  • PostgreSQL 7.4
    • ALTER TABLE ... CLUSTER ON ... syntax added (commit 432b9b0f)
  • PostgreSQL 7.0
    • ALTER TABLE ... DROP COLUMN ... added (initial commit fa5400c0)

Examples

Basic usage example for ALTER TABLE:

postgres=# CREATE TABLE foo (id int);
CREATE TABLE

postgres=# ALTER TABLE foo RENAME TO bar;
ALTER TABLE

Categories

DDL, SQL command

See also

CREATE TABLE, DROP TABLE, ALTER FOREIGN TABLE