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.

CREATE TABLE AS adds a _rowid primary key column

Tables created by CREATE TABLE ... AS SELECT or SELECT ... INTO get an extra internal _rowid column prepended to the projected columns. It is visible in SELECT * and in information_schema.columns, so SELECT * returns one more column than PostgreSQL would:

SQL

CREATE TABLE ctas AS SELECT 1 AS a, 'x' AS b;
SELECT * FROM ctas;   -- returns _rowid, a, b

CREATE TABLE tgt (a INT, b TEXT);
INSERT INTO tgt SELECT * FROM ctas;
-- ERROR: INSERT has more target columns than expressions (2 columns, 3 values) (42601)

The error message’s wording is inverted — the surplus value is _rowid.

_rowid becomes the table’s primary key and cannot be removed afterwards (ALTER TABLE ctas DROP COLUMN _rowid fails with Cannot drop primary key column '_rowid'). Selecting columns explicitly (SELECT a, b FROM ctas) avoids the problem per query; to avoid the column entirely, create the table first and populate it separately:

SQL

CREATE TABLE clean (a INT, b TEXT);
INSERT INTO clean SELECT a, b FROM source;

Tables created with a plain CREATE TABLE are unaffected, with or without a primary key.

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

Identity columns ignore sequence options

Identity columns always start at 1 and increment by 1. Sequence options in an identity definition either fail to parse or have no effect:

• More than one option is a parse error — the PostgreSQL form IDENTITY (START WITH 100 INCREMENT BY 10) fails with SQL parse error: Expected ), found: INCREMENT (42601). The comma-separated form fails too.
• A single option such as IDENTITY (START WITH 100) or IDENTITY (MINVALUE 5) is accepted, but silently ignored — the generated values are still 1, 2, 3, …. This applies to all of START, INCREMENT, MINVALUE, MAXVALUE, CACHE, and CYCLE.

For a specific starting value or step, use an explicit sequence — CREATE SEQUENCE does honor START and INCREMENT:

SQL

CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 5;
CREATE TABLE orders (id BIGINT PRIMARY KEY DEFAULT nextval('order_seq'), tag TEXT);
-- ids: 1000, 1005, 1010, …

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

-- 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 hnsw index building is gated off in the current release — see the pgvector page. IF NOT EXISTS is supported.

Other index access methods are rejected

GiST, Hash, SP-GiST, and BRIN are rejected at CREATE INDEX time — they are not accepted-then-ignored:

ERROR: access method "gist" is not supported (0A000)
HINT:  Only btree, gin, and hnsw indexes are currently supported.

The hint lists hnsw even though HNSW index building is currently disabled.

hnsw builds vector indexes and has extra structural requirements of its own — see pgvector and the compatibility matrix.

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

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.

Dropping a table without CASCADE does not refuse — it leaves dependents broken

PostgreSQL refuses to drop a table that other objects depend on unless you pass CASCADE. DB9 drops it anyway, and dependents are neither dropped nor updated.

A dependent view stays in pg_class / pg_views and fails only when queried:

SQL

CREATE TABLE d1 (a INT);
CREATE VIEW dv1 AS SELECT * FROM d1;

DROP TABLE d1;        -- succeeds (PostgreSQL would raise a dependency error)
SELECT * FROM dv1;    -- ERROR: relation "d1" does not exist (42P01)

A dependent materialized view is worse — it keeps serving its last-materialized rows with no error — REFRESH is what surfaces it:

SQL

CREATE TABLE m1 (a INT);
INSERT INTO m1 VALUES (1), (2), (3);
CREATE MATERIALIZED VIEW mv1 AS SELECT * FROM m1;

DROP TABLE m1;                     -- succeeds
SELECT count(*) FROM mv1;          -- still returns 3 — stale data, no error
REFRESH MATERIALIZED VIEW mv1;
-- ERROR: materialized view source relation 'public.m1' no longer exists (XX000)

Always pass CASCADE when you intend to remove dependents, and check for dependent views and materialized views before dropping a table.

Other DDL

Statement	Supported
CREATE SCHEMA	Yes
CREATE SEQUENCE / DROP SEQUENCE	Yes
ALTER SEQUENCE	Ownership forms only — see below
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, function, extension, index, schema)	Yes

COMMENT ON accepts only six object types

TABLE, COLUMN, FUNCTION, EXTENSION, INDEX, and SCHEMA are supported. VIEW, SEQUENCE, TYPE, DATABASE, and CONSTRAINT are rejected:

ERROR: Unsupported COMMENT ON statement (0A000)

Where the statement does succeed, the comment is written to pg_description, but OBJ_DESCRIPTION() and COL_DESCRIPTION() still return NULL — read pg_description directly. See description functions.

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.

ALTER SEQUENCE supports only the ownership forms

These three are accepted and take effect:

SQL

ALTER SEQUENCE order_seq OWNER TO app_user;       -- changes pg_class.relowner
ALTER SEQUENCE order_seq OWNED BY orders.id;      -- sequence is dropped with the table
ALTER SEQUENCE order_seq OWNED BY NONE;           -- clears it; sequence survives the table

Every other form is rejected:

ERROR: ALTER SEQUENCE not supported (0A000)

That covers the parameter clauses (RESTART, RESTART WITH, INCREMENT BY, START WITH, MINVALUE, NO MINVALUE, MAXVALUE, NO MAXVALUE, CACHE, CYCLE, NO CYCLE), the type and storage clauses (AS, SET LOGGED, SET UNLOGGED), and RENAME TO and SET SCHEMA. A sequence’s parameters, name, and schema are therefore all fixed for its lifetime.

To reposition a sequence, use SETVAL() instead of RESTART:

SQL

SELECT SETVAL('order_seq', 1000);         -- next NEXTVAL returns 1001 (at INCREMENT 1)
SELECT SETVAL('order_seq', 1000, false);  -- next NEXTVAL returns 1000

Changing INCREMENT or the bounds requires dropping and recreating the sequence — but read the warning below first if anything already references it.

DROP SEQUENCE does not check column defaults

Unlike PostgreSQL, DB9 does not refuse to drop a sequence that a column default still depends on. The DROP succeeds silently and leaves the table broken:

SQL

CREATE SEQUENCE ref_s;
CREATE TABLE ref_t (id BIGINT PRIMARY KEY DEFAULT nextval('ref_s'), tag TEXT);

DROP SEQUENCE ref_s;                      -- succeeds; PostgreSQL would refuse
INSERT INTO ref_t (tag) VALUES ('x');     -- ERROR: relation "public.ref_s" does not exist (42P01)

PostgreSQL would reject that DROP with “cannot drop sequence ref_s because other objects depend on it”.

SERIAL columns are affected the same way, which is the more common case — the sequence CREATE TABLE t (id SERIAL …) creates implicitly can be dropped just as freely:

SQL

CREATE TABLE ser_t (id SERIAL PRIMARY KEY, tag TEXT);
DROP SEQUENCE ser_t_id_seq;               -- succeeds
INSERT INTO ser_t (tag) VALUES ('x');     -- ERROR: relation "public.ser_t_id_seq" does not exist (42P01)

So check for column defaults, SERIAL, and identity columns before dropping a sequence. If you are recreating one to change its parameters, recreate it under the same name and restore its position with SETVAL() — reading last_value first, so you do not hand out duplicate keys:

SQL

SELECT last_value FROM order_seq;             -- note this before dropping
-- ... DROP SEQUENCE / CREATE SEQUENCE with the new parameters ...
SELECT SETVAL('order_seq', <last_value>);     -- restore the position
ALTER SEQUENCE order_seq OWNED BY orders.id;  -- re-establish the link, if it had one

That last line matters: a recreated sequence has no OWNED BY link, so without it the sequence will outlive the table it belongs to.

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.