Skip to content
Discord Get Started

DDL — Data Definition

SQL Reference: DDL

Section titled “SQL Reference: DDL”

Data Definition Language statements for creating and managing database objects.

CREATE TABLE

Section titled “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);

Generated (computed) columns:

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

CREATE INDEX

Section titled “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);


-- 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);

Only btree (default), gin, and hnsw are recognized index access methods, and all three can be created — see the pgvector page for HNSW. IF NOT EXISTS is supported.

GIN index example (JSONB containment):

SQL
CREATE TABLE documents (id SERIAL PRIMARY KEY, metadata JSONB);
CREATE INDEX idx_metadata ON documents USING GIN (metadata);


-- Use the @> containment operator (accelerated by GIN)
SELECT * FROM documents WHERE metadata @> '{"type": "pdf"}';

GIN indexes support JSONB containment (@>) and full-text search (@@) operators. See Full-Text Search for tsvector usage.

CREATE VIEW

Section titled “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

Section titled “CREATE MATERIALIZED VIEW”
SQL
CREATE MATERIALIZED VIEW mv_name AS SELECT ...;
REFRESH MATERIALIZED VIEW mv_name;

ALTER TABLE

Section titled “ALTER TABLE”
OperationSyntax
Add columnALTER TABLE t ADD COLUMN col type
Drop columnALTER TABLE t DROP COLUMN col
Rename columnALTER TABLE t RENAME COLUMN old TO new
Rename tableALTER TABLE t RENAME TO new_name
Set defaultALTER TABLE t ALTER COLUMN col SET DEFAULT val
Drop defaultALTER TABLE t ALTER COLUMN col DROP DEFAULT
Set not nullALTER TABLE t ALTER COLUMN col SET NOT NULL
Drop not nullALTER TABLE t ALTER COLUMN col DROP NOT NULL
Change typeALTER TABLE t ALTER COLUMN col SET DATA TYPE type [USING expr] — not on primary-key or foreign-key referencing columns, see below
Add constraintALTER TABLE t ADD CONSTRAINT name ...
Drop constraintALTER TABLE t DROP CONSTRAINT name
Rename constraintALTER TABLE t RENAME CONSTRAINT old TO new

DROP Statements

Section titled “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.

TRUNCATE t and TRUNCATE TABLE t are the only accepted spellings. Every optional clause is rejected at parse time (42601) — the multi-table form TRUNCATE a, b, plus CASCADE, RESTRICT, ONLY, RESTART IDENTITY and CONTINUE IDENTITY (which is PostgreSQL’s default, so it is only ever written explicitly).

TRUNCATE on a table that another table’s foreign key references is refused, as in PostgreSQL, with 0A000 (cannot truncate a table referenced in a foreign key constraint) — even when the referencing table is empty. Because the multi-table form and CASCADE are unavailable, empty the referencing table first and remove the parent’s rows with DELETE, or drop the foreign key before truncating — see Compatibility matrix.

Other DDL

Section titled “Other DDL”
StatementSupported
CREATE SCHEMAYes
CREATE SEQUENCE / DROP SEQUENCEYes
ALTER SEQUENCEOwnership forms only — see below
CREATE TYPE (enum, composite)Yes — but enum values sort by label text, and composite values cannot be read field-wise; see Custom Types and the composite type caution
ALTER TYPEEnum values and RENAME TO only — see below
DROP TYPEYes — but CASCADE does not cascade, and some dependents are not checked; see below
CREATE FUNCTION / DROP FUNCTIONYes
CREATE TRIGGER / DROP TRIGGERYes
CREATE COLLATION / DROP COLLATIONYes
CREATE DATABASE / DROP DATABASEYes
ALTER DATABASERENAME TO and OWNER TO only — see below
CREATE EXTENSION / DROP EXTENSIONYes
COMMENT ON (table, column, function, extension, index, schema)Yes

Sequence options: START, INCREMENT, MINVALUE, MAXVALUE, CACHE, CYCLE are accepted in CREATE SEQUENCE (START and INCREMENT are confirmed to take effect). In identity column definitions a single option is accepted but ignored, and multiple options fail to parse — see Identity columns above.

Row-Level Security DDL

Section titled “Row-Level Security DDL”
SQL
-- Enable / disable RLS on a table
ALTER TABLE t ENABLE ROW LEVEL SECURITY;
ALTER TABLE t DISABLE ROW LEVEL SECURITY;


-- Force RLS for table owner
ALTER TABLE t FORCE ROW LEVEL SECURITY;
ALTER TABLE t NO FORCE ROW LEVEL SECURITY;


-- Create, alter, and drop policies
CREATE POLICY name ON t FOR SELECT USING (expr);
CREATE POLICY name ON t FOR INSERT WITH CHECK (expr);
CREATE POLICY name ON t AS RESTRICTIVE FOR SELECT USING (expr);
ALTER POLICY name ON t USING (new_expr);
DROP POLICY [IF EXISTS] name ON t;

See Row-Level Security for complete policy semantics and examples.