ALTER TABLE

Modify an existing table: add/remove columns, change types, rename, and manage constraints.

Overview

ALTER TABLE lets you modify the structure of an existing table without dropping and recreating it. This is essential for evolving a schema after the application is in production. You can add new columns, drop columns, change column types, rename columns, set/drop defaults, and add or remove constraints all with a single statement family. PostgreSQL executes ALTER TABLE transactionally (with some exceptions for large structural changes). Adding a column with a DEFAULT value in PostgreSQL 11+ is an instant metadata-only operation older versions would rewrite the entire table, which was very slow on large tables. When changing a column's data type, PostgreSQL needs to verify that all existing values can be cast to the new type. If the implicit cast does not exist, you must provide a USING expression telling PostgreSQL how to perform the conversion.

Key Points

•ADD COLUMN: adds a new column; specifying DEFAULT makes it instant in PostgreSQL 11+
•DROP COLUMN: removes a column and all its data permanently
•RENAME COLUMN: renames a column without changing data
•ALTER COLUMN TYPE: changes data type; use USING for custom cast expressions
•SET DEFAULT / DROP DEFAULT: changes the column default value
•ADD CONSTRAINT / DROP CONSTRAINT: adds or removes named constraints

Examples

Add and drop columns

-- Add a column
ALTER TABLE users ADD COLUMN last_login TIMESTAMPTZ;

-- Add with default (instant in PG 11+)
ALTER TABLE users ADD COLUMN score INTEGER NOT NULL DEFAULT 0;

-- Drop a column
ALTER TABLE users DROP COLUMN old_field;

-- Drop with cascade (removes dependent views/constraints too)
ALTER TABLE users DROP COLUMN email CASCADE;

Rename a table and a column

-- Rename a column
ALTER TABLE users RENAME COLUMN username TO display_name;

-- Rename the table itself
ALTER TABLE users RENAME TO accounts;

Change a column type

-- Simple type change (implicit cast exists)
ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(12,2);

-- Type change requiring a USING expression
ALTER TABLE events
  ALTER COLUMN started_at TYPE TIMESTAMPTZ
  USING started_at AT TIME ZONE 'UTC';

ALTER TABLE

Overview

Key Points

Examples

Exercises

Knowledge Check

In PostgreSQL 11+, what happens when you ADD COLUMN with a non-NULL DEFAULT?