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 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)
    • 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.2
    • NOT VALID option added to ALTER TABLE ... ADD constraint (commit 722bf701)
    • ALTER TABLE VALIDATE constraint added (commit 722bf701)
  • PostgreSQL 8.0
    • ALTER TABLE ... ADD COLUMN with defaults and NOT NULL constraints (commit 077db40f)
    • column types can be altered (commit 077db40f)
    • multiple ALTER actions in a single ALTER TABLE command supported (commit 077db40f)

Categories

DDL

See also

CREATE TABLE, DROP TABLE, ALTER FOREIGN TABLE