Alter Table

The ALTER TABLE statement is the primary way to modify the structure of an existing PostgreSQL table. It covers a wide range of operations: adding or removing columns, changing column types and defaults, enforcing or removing constraints, and renaming the table itself. Understanding which operations require a table rewrite or an exclusive lock is critical for production deployments.

Common ALTER TABLE actions

-- Add a column
ALTER TABLE table_name ADD COLUMN column_name datatype constraint;

-- Drop a column
ALTER TABLE table_name DROP COLUMN column_name;

-- Rename a column
ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

-- Change column default
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT value;
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

-- Change NOT NULL
ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;
ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;

-- Add a CHECK constraint
ALTER TABLE table_name ADD CHECK (expression);

-- Add a named constraint
ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition;

-- Rename the table
ALTER TABLE table_name RENAME TO new_table_name;

ALTER TABLE examples

Set up a demo table:

DROP TABLE IF EXISTS links;
CREATE TABLE links (
  link_id SERIAL PRIMARY KEY,
  title   VARCHAR(512) NOT NULL,
  url     VARCHAR(1024) NOT NULL
);

Add, drop, and rename columns:

-- Add a boolean column
ALTER TABLE links ADD COLUMN active BOOLEAN;

-- Remove it
ALTER TABLE links DROP COLUMN active;

-- Rename title to link_title
ALTER TABLE links RENAME COLUMN title TO link_title;

Add a column with a default value:

ALTER TABLE links ADD COLUMN target VARCHAR(10);
ALTER TABLE links ALTER COLUMN target SET DEFAULT '_blank';

Insert a row — the default kicks in:

INSERT INTO links (link_title, url)
VALUES ('PostgreSQL Tutorial', 'https://neon.com/postgresql/');

SELECT * FROM links;
 link_id |       link_title        |               url                | target
---------+-------------------------+----------------------------------+--------
       1 | PostgreSQL Tutorial     | https://neon.com/postgresql/     | _blank
(1 row)

Add a CHECK constraint restricting the allowed target values:

ALTER TABLE links
ADD CHECK (target IN ('_self', '_blank', '_parent', '_top'));

Attempting an invalid value now raises an error:

INSERT INTO links (link_title, url, target)
VALUES ('PostgreSQL', 'http://www.postgresql.org/', 'whatever');
-- ERROR: new row for relation "links" violates check constraint "links_target_check"

Add a UNIQUE constraint and rename the table:

ALTER TABLE links ADD CONSTRAINT unique_url UNIQUE (url);
ALTER TABLE links RENAME TO urls;

ALTER TABLE tips

• Adding a nullable column with no default is a metadata-only change in PostgreSQL and does not rewrite the table — it is safe to run on large production tables.
• Adding a column with a non-volatile default (a constant value) is also metadata-only in PostgreSQL 11+.
• Changing a column's data type or adding a NOT NULL constraint typically rewrites the table and takes an exclusive lock — plan these operations during low-traffic windows or use tools like pg_repack.
• Use named constraints (ADD CONSTRAINT name ...) so you can drop them easily later with DROP CONSTRAINT name.

Reference: PostgreSQL documentation — ALTER TABLE.