DDL — Data Definition

SQL Reference: DDL

Data Definition Language statements for creating and managing database objects.

CREATE TABLE

SQL

CREATE TABLE [IF NOT EXISTS] table_name (
    column_name type [constraints],
    ...
    [table_constraints]
);

-- Create from query result
CREATE TABLE new_table AS SELECT ... FROM source;
SELECT ... INTO new_table FROM source;

Column constraints: NOT NULL, DEFAULT, PRIMARY KEY, UNIQUE, REFERENCES (foreign key), CHECK

Table constraints: PRIMARY KEY (col, ...), UNIQUE (col, ...), FOREIGN KEY (col) REFERENCES table(col) with ON DELETE/ON UPDATE actions, CHECK (expr)

SERIAL and BIGSERIAL columns are supported for auto-incrementing IDs. Self-referential foreign keys are supported.

Identity columns:

SQL

-- GENERATED ALWAYS AS IDENTITY (database controls the value)
CREATE TABLE t (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

-- GENERATED BY DEFAULT AS IDENTITY (user can override)
CREATE TABLE t (id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY);

-- With sequence options
CREATE TABLE t (
  id INT GENERATED ALWAYS AS IDENTITY (START WITH 100 INCREMENT BY 10)
);

Generated (computed) columns:

SQL

CREATE TABLE products (
  price NUMERIC,
  tax NUMERIC,
  total NUMERIC GENERATED ALWAYS AS (price + tax) STORED
);

CREATE INDEX

SQL

-- B-tree index (default)
CREATE INDEX idx_name ON table (column);

-- Unique index
CREATE UNIQUE INDEX idx_name ON table (column);

-- GIN index (for JSONB, full-text search, arrays)
CREATE INDEX idx_name ON table USING GIN (column);

-- GiST index
CREATE INDEX idx_name ON table USING GIST (column);

-- Expression index
CREATE INDEX idx_name ON table (lower(column));

-- Partial index
CREATE INDEX idx_name ON table (column) WHERE active = true;

-- Non-blocking index creation
CREATE INDEX CONCURRENTLY idx_name ON table (column);

Index methods: btree (default), GIN, GiST. IF NOT EXISTS is supported.

CREATE VIEW

SQL

CREATE VIEW view_name AS SELECT ...;
CREATE OR REPLACE VIEW view_name AS SELECT ...;

CREATE OR REPLACE VIEW cannot drop or rename existing columns; only appending new columns is allowed.

CREATE MATERIALIZED VIEW

SQL

CREATE MATERIALIZED VIEW mv_name AS SELECT ...;
REFRESH MATERIALIZED VIEW mv_name;

ALTER TABLE

Operation	Syntax
Add column	ALTER TABLE t ADD COLUMN col type
Drop column	ALTER TABLE t DROP COLUMN col
Rename column	ALTER TABLE t RENAME COLUMN old TO new
Rename table	ALTER TABLE t RENAME TO new_name
Set default	ALTER TABLE t ALTER COLUMN col SET DEFAULT val
Drop default	ALTER TABLE t ALTER COLUMN col DROP DEFAULT
Set not null	ALTER TABLE t ALTER COLUMN col SET NOT NULL
Drop not null	ALTER TABLE t ALTER COLUMN col DROP NOT NULL
Change type	ALTER TABLE t ALTER COLUMN col SET DATA TYPE type [USING expr]
Add constraint	ALTER TABLE t ADD CONSTRAINT name ...
Drop constraint	ALTER TABLE t DROP CONSTRAINT name
Rename constraint	ALTER TABLE t RENAME CONSTRAINT old TO new

DROP Statements

SQL

DROP TABLE [IF EXISTS] table_name [CASCADE];
DROP INDEX [IF EXISTS] index_name;
DROP VIEW [IF EXISTS] view_name [CASCADE];
DROP MATERIALIZED VIEW [IF EXISTS] mv_name [CASCADE];
TRUNCATE TABLE table_name;

CASCADE transitively resolves and drops all dependent views and materialized views.

Other DDL

Statement	Supported
CREATE SCHEMA	Yes
CREATE SEQUENCE / DROP SEQUENCE	Yes
CREATE TYPE (enum, composite) / ALTER TYPE / DROP TYPE	Yes
CREATE FUNCTION / DROP FUNCTION	Yes
CREATE TRIGGER / DROP TRIGGER	Yes
CREATE COLLATION / DROP COLLATION	Yes
CREATE DATABASE / DROP DATABASE / ALTER DATABASE	Yes
CREATE EXTENSION / DROP EXTENSION	Yes
COMMENT ON (table, column, schema, etc.)	Yes

Sequence options: START, INCREMENT, MINVALUE, MAXVALUE, CACHE, CYCLE are supported in CREATE SEQUENCE and identity column definitions.