DDL — Data Definition

SQL Reference: DDL

Data Definition Language statements for creating and managing database objects.

CREATE TABLE

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;

CREATE TABLE ... AS does not accept PostgreSQL’s trailing WITH DATA or WITH NO DATA: either fails with 42601 (syntax error at or near "WITH"). For an empty table with the query’s columns, add WHERE false to the query instead; the table still gets the _rowid column described below.

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:

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 dropping the column removes that key and its index along with it. The table stays writable afterwards, and SELECT * matches PostgreSQL again:

ALTER TABLE ctas DROP COLUMN _rowid;
SELECT * FROM ctas;                      -- returns a, b
INSERT INTO tgt SELECT * FROM ctas;      -- now works

That is the shortest fix once the table exists, except on a database that has ever had a logical replication slot: there, dropping _rowid from a table that holds rows fails with 55000 (see Realtime capture below). Selecting columns explicitly (SELECT a, b FROM ctas) avoids the problem per query without altering the table, and declaring the table up front keeps the column from ever appearing:

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:

-- Alternatives: ALWAYS refuses a user-supplied id, BY DEFAULT accepts one (see the caution below)
CREATE TABLE t (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);
CREATE TABLE t (id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY);

GENERATED ALWAYS has no override clause

As in PostgreSQL, ALWAYS refuses an explicit value, on INSERT and on UPDATE:

CREATE TABLE t (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, v TEXT);
INSERT INTO t (v) VALUES ('auto');              -- id 1
INSERT INTO t (id, v) VALUES (50, 'manual');
-- ERROR: cannot insert a non-DEFAULT value into column "id"   (428C9)
-- HINT:  Use OVERRIDING SYSTEM VALUE to override.

UPDATE t SET id = 99 fails the same way (428C9, column "id" can only be updated to DEFAULT). The clause the hint recommends does not parse in DB9, so no INSERT can write an explicit id into an ALWAYS column:

INSERT INTO t (id, v) OVERRIDING SYSTEM VALUE VALUES (60, 'ovr');
-- ERROR: syntax error at or near "OVERRIDING"   (42601)

Converting the column afterwards is not possible either — ALTER TABLE … ALTER COLUMN … SET GENERATED BY DEFAULT and DROP IDENTITY are rejected (42601). Only COPY … FROM STDIN writes the supplied ids, as it does in PostgreSQL. So when rows arrive with their own ids as INSERT statements — an import, a backfill, a copy from another database — create the column as GENERATED BY DEFAULT AS IDENTITY, which accepts them.

The sharp edge with BY DEFAULT is that a manual value does not advance the counter, so generated values walk into it later:

CREATE TABLE tb (id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, v TEXT);
INSERT INTO tb (id, v) VALUES (50, 'manual');   -- accepted
INSERT INTO tb (v) SELECT 'bulk' || g FROM generate_series(1, 60) g;
-- ERROR: duplicate key value violates unique constraint "tb_pkey"  (23505)
-- DETAIL: Key (id)=(50) already exists.

After inserting explicit ids, reposition the backing sequence — it is named <table>_<column>_seq and SETVAL() works on it:

SELECT setval('tb_id_seq', 50);   -- next generated id is 51

An identity definition takes PostgreSQL’s sequence options — START WITH, INCREMENT BY, MINVALUE, MAXVALUE, CYCLE / NO CYCLE, CACHE and SEQUENCE NAME — alone or several together, separated by spaces:

CREATE TABLE ti (id INT GENERATED ALWAYS AS IDENTITY (START WITH 100 INCREMENT BY 10) PRIMARY KEY, v TEXT);
-- ids: 100, 110, 120, …

As in PostgreSQL, separating the options with commas — IDENTITY (START WITH 100, INCREMENT BY 10) — fails to parse (syntax error at or near ",", 42601). The backing sequence is named <table>_<column>_seq unless SEQUENCE NAME gives another, and ALTER SEQUENCE changes it in place later: after ALTER SEQUENCE ti_id_seq RESTART WITH 500, the next id is 500.

An explicit sequence works too — CREATE SEQUENCE honors START and INCREMENT:

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:

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

CREATE INDEX

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

-- Hash index (equality lookups on integer and OID columns)
CREATE INDEX idx_name ON table USING HASH (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);

btree (default), hash, gin, and hnsw are the recognized index access methods, and all of them can be created — subject to the column-type restrictions noted below for hash and gin. See the pgvector page for HNSW. IF NOT EXISTS is supported.

GiST, 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, hash, gin, and hnsw indexes are currently supported.

ivfflat fails one step earlier, with access method "ivfflat" does not exist (42704) — the same response an invented name gets, so the code does not distinguish “planned but unavailable” from “not a thing”.

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

hash indexes cover integer and OID columns only

A hash index is a real index: it appears in pg_indexes, hash is registered in pg_am, and the planner uses it for equality — EXPLAIN reports Index Scan using idx_name. The column type is the DDL-time restriction:

CREATE INDEX idx_id  ON t USING HASH (id);    -- OID, SMALLINT, INTEGER, BIGINT
CREATE INDEX idx_tag ON t USING HASH (tag);
-- ERROR: hash indexes currently support only integer and OID columns (0A000)

As in PostgreSQL, a hash index can be neither unique nor multicolumn: CREATE UNIQUE INDEX … USING HASH fails with access method "hash" does not support unique indexes, and a two-column list with access method "hash" does not support multicolumn indexes. Both are 0A000.

One runtime limit has no error to announce it: the index serves a single-value equality predicate (n = 42, and n IN (42)), but a multi-element IN list falls back to a sequential scan where a btree index on the same column still gives an Index Scan. Check EXPLAIN before relying on a hash index for a list lookup.

btree is the general-purpose choice — it indexes every type, enforces uniqueness, spans several columns, and answers both single-value and IN-list equality. Reach for hash when the column is an integer key you only ever compare with a single = value.

Every btree index entry and every primary key must encode to under 6,963 bytes — DB9’s distributed storage caps a key at 8,192 bytes and reserves the remainder for encoding overhead. This is a limit on the value, not the column type. PostgreSQL has a btree key limit too (index row size 3024 exceeds btree version 4 maximum 2704), but it compresses index keys first, so the two engines diverge in both directions: a long compressible value — repeat('a', 10000) — is accepted by PostgreSQL and rejected here, while for incompressible values PostgreSQL’s 2,704-byte ceiling is the stricter of the two. DB9 does no such compression, so a value’s content never affects where it falls.

The cap covers the whole encoded key — the indexed columns plus per-index overhead, and for a non-unique index the primary key as well — so a multi-column index is bounded by its combined width, not per column. For one TEXT column with an INTEGER primary key the cutoff lands at 6,152 bytes of text: 6,152 bytes insert, 6,153 fails. Bytes, not characters — 3,076 two-byte UTF-8 characters reach the same cutoff. VARCHAR behaves exactly like TEXT, and both transports agree.

Two things move that number, so treat it as a ceiling to stay well under rather than a budget to spend. A non-unique index carries the primary key in every entry, so a wider key costs you value bytes; a UNIQUE index does not carry it and is unaffected:

Indexed column	Primary key	Index	Largest value that indexes
`TEXT`	`INTEGER`	plain	6,152 bytes
`TEXT`	`INTEGER`	`UNIQUE`	6,152 bytes
`TEXT`	`BIGINT`	plain	6,144 bytes
`TEXT`	`BIGINT`	`UNIQUE`	6,152 bytes
`VECTOR(n)`	`INTEGER` or `BIGINT`	plain	864 dimensions
`VECTOR(n)`	`INTEGER` or `BIGINT`	`UNIQUE`	865 dimensions

Encoding overhead differs per type, so do not derive one row from another: a vector costs far more per element than a character of text, and widening the key to BIGINT costs 8 bytes of a TEXT value’s budget while being absorbed by encoding headroom for vectors.

A value that is too large to index can still be stored — the limit only applies once the value reaches a key. An unindexed TEXT column accepts a 10 KB string, and it keeps that row even if you then try to index the column: it is the CREATE INDEX build that fails, not the row. Rows are rejected only by an INSERT or UPDATE through an index that already exists. Which message you get depends on where the key was built:

-- INSERT or UPDATE through an existing index, or an oversized UNIQUE key
ERROR: index row exceeds server key limit (54000)

-- An oversized value in a TEXT PRIMARY KEY
ERROR: storage key exceeds server limit (54000)

Both share SQLSTATE 54000, so match on the code rather than the text — with one exception. A TEXT or VARCHAR primary-key value only slightly over the limit gets a bare internal error (XX000) instead: with a single-column text key, values up to 6,440 bytes insert, 6,441 to 7,256 bytes fail with XX000, and 7,257 bytes or more get the 54000 above. Treat XX000 on an INSERT or UPDATE of a long text key as this limit. CREATE INDEX over existing oversized rows fails the build with a third, far more detailed variant that names the actual and maximum sizes and suggests the workarounds:

ERROR: index row requires 11290 bytes, exceeds maximum 6963 bytes
       (TiKV limit 8192 minus internal encoding overhead) (54000)
HINT:  Values larger than ~6963 bytes cannot be indexed with btree. Consider an
       expression index on a prefix (e.g. CREATE INDEX ON t (left(col, 200))), a
       GIN index for full-text search, or removing the btree index on this column.

A failed CREATE INDEX leaves the table’s rows untouched; only the index build fails. Both suggested workarounds work — index a prefix when you need equality or range lookups, or index to_tsvector(...) with GIN when you need to search the text:

-- Equality / range on the first 200 characters
CREATE INDEX idx_body_prefix ON documents (left(body, 200));

-- Full-text search over the whole column, no size limit
CREATE INDEX idx_body_fts ON documents USING GIN (to_tsvector('english', body));

Because the index build runs as an asynchronous job, the error above is also recorded in db9_ddl_jobs(), which is the server’s own suggestion for inspecting a failed build before you retry. One row per DDL job, with phase, error_sqlstate and the full last_error:

SELECT job_id, relation_name, object_name, kind, phase, error_sqlstate, attempts
FROM db9_ddl_jobs()
ORDER BY job_id DESC;

GIN index example (JSONB containment):

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.

GIN has a column-type restriction of its own: it builds only over JSONB, TSVECTOR, and array types. On any other column — including JSON, TEXT, and INTEGER — CREATE INDEX fails with the same error PostgreSQL gives, data type text has no default operator class for access method "gin" (42704). To index text, index the to_tsvector(...) expression rather than the column. See the compatibility matrix for the full list of index restrictions.

CREATE VIEW

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

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

A materialized view is always populated when it is created. PostgreSQL’s trailing WITH NO DATA, which creates the view empty for a later REFRESH to fill, is not parsed, and neither is WITH DATA: both fail with 42601 (syntax error at or near "WITH"), so leave the clause out.

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]` — not on primary-key columns, and on a foreign key’s referencing column only `INTEGER` ↔ `BIGINT` and `VARCHAR(n)` → `TEXT`; see below
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`
Disable trigger	`ALTER TABLE t DISABLE TRIGGER {name \| USER \| ALL}` — prefer `USER`; `ALL` is refused on any table a foreign key touches, see disabling triggers
Enable trigger	`ALTER TABLE t ENABLE TRIGGER {name \| USER \| ALL}` — prefer `USER`; `ALL` is refused on any table a foreign key touches, see disabling triggers
Enable as replica/always	`ALTER TABLE t ENABLE {REPLICA \| ALWAYS} TRIGGER name` — a name is required; `USER`/`ALL` give `42601 REPLICA and ALWAYS require a trigger name`

Every form above also accepts the optional ONLY keyword (ALTER TABLE ONLY t …), with the same effect, as do the row-level security forms. A pg_dump that writes ALTER TABLE ONLY therefore restores as-is.

Creating a logical replication slot turns on Realtime capture for the whole database, and it stays on after the slot, and its publication, are dropped. A publication on its own does not turn it on. While capture is on, statements that rewrite stored rows outside the change journal are refused:

ERROR: unjournaled restore or physical row rewrite is not supported while Realtime capture is enabled   (55000)

That covers ALTER TABLE ... ADD PRIMARY KEY on a table that already has rows — including the ALTER TABLE ONLY ... ADD CONSTRAINT ... PRIMARY KEY step of a pg_dump import — dropping the primary key of a table that has rows (DROP CONSTRAINT <table>_pkey, or DROP COLUMN _rowid on a CREATE TABLE ... AS table), and ALTER TYPE ... RENAME VALUE on a label that stored rows use. Each of them still works while no stored row is involved — on an empty table, or for a label no row uses — so add the primary key before loading the rows, or keep slots off the database where you run these statements.

CREATE TABLE ... AS, SELECT ... INTO, CREATE MATERIALIZED VIEW and REFRESH MATERIALIZED VIEW keep working under capture. A wal2json slot receives the rows that CREATE TABLE ... AS and SELECT ... INTO load as I changes for the new table, _rowid included, even though that table is in no publication; a materialized view’s contents are never emitted. See Logical decoding (CDC) for what a slot provides.

ALTER COLUMN … TYPE is refused with 0A000 whenever the column is part of the primary key, whether the change widens or narrows the type. The referencing column of a foreign key accepts two changes, INTEGER ↔ BIGINT and VARCHAR(n) → TEXT, without a USING clause, and the foreign key keeps enforcing (23503). Any other change to it — VARCHAR(10) → VARCHAR(20), INTEGER → NUMERIC or TEXT, or any change with USING — is refused with 0A000. The message names the column and the reason:

CREATE TABLE parent (code VARCHAR(10) PRIMARY KEY);
CREATE TABLE child  (id INT PRIMARY KEY, pcode VARCHAR(10) REFERENCES parent(code));

ALTER TABLE parent ALTER COLUMN code  TYPE VARCHAR(20);
-- ERROR: Cannot alter type of primary key column 'code' (0A000)
ALTER TABLE child  ALTER COLUMN pcode TYPE VARCHAR(20);
-- ERROR: Cannot alter type of column 'pcode' used in foreign key (0A000)
ALTER TABLE child  ALTER COLUMN pcode TYPE TEXT;         -- ALTER TABLE

For a primary key, the column’s role is what matters, not the type and not the rest of the table; a foreign key’s referencing column also depends on the target type. Measured on both the wire protocol and the HTTP SQL API:

Column participates in	`ALTER COLUMN … TYPE`
Nothing	Works
`PRIMARY KEY` (single-column)	`0A000` `Cannot alter type of primary key column`
`PRIMARY KEY` (one member of a composite key)	`0A000` `Cannot alter type of primary key column`
`FOREIGN KEY` (the referencing column)	`INTEGER` ↔ `BIGINT` and `VARCHAR(n)` → `TEXT` work, and the foreign key still enforces (`23503`); any other change is `0A000` `Cannot alter type of column … used in foreign key`
`FOREIGN KEY` (the referenced column, when it is the primary key)	`0A000` `Cannot alter type of primary key column`
`FOREIGN KEY` (the referenced column, when it is `UNIQUE` but not the primary key)	Works — the foreign key still enforces (`23503`)
`UNIQUE` constraint	Works
A non-unique index	Works
`CHECK` constraint	Works
`NOT NULL`	Works
Nothing, in a table that has a key elsewhere	Works

So UNIQUE columns change type freely while primary keys do not, even though both are index-backed — and that holds when a foreign key points at the UNIQUE column. A non-key column is unaffected by keys elsewhere in the same table.

To change a key column’s type, recreate the table: create the replacement with the new types, copy the rows, then drop and rename. A referenced table cannot be dropped while a foreign key points at it, so drop those constraints first or pass CASCADE — see DROP Statements.

DROP Statements

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 [RESTART IDENTITY | CONTINUE IDENTITY] [CASCADE | RESTRICT];

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

TRUNCATE t and TRUNCATE TABLE t are the accepted spellings, each optionally followed by RESTART IDENTITY or CONTINUE IDENTITY, and by CASCADE or RESTRICT. Both identity clauses take effect:

CREATE TABLE t (id SERIAL PRIMARY KEY, v TEXT);
INSERT INTO t (v) VALUES ('a'), ('b'), ('c');

TRUNCATE t RESTART IDENTITY;    -- the next INSERT gets id 1
TRUNCATE t CONTINUE IDENTITY;   -- the next INSERT continues from where the sequence stood

CONTINUE IDENTITY is PostgreSQL’s default, so it is only ever written explicitly. CASCADE and RESTRICT work as in PostgreSQL: TRUNCATE parent CASCADE also empties every table whose foreign key references parent, transitively, and TRUNCATE parent RESTART IDENTITY CASCADE resets the sequences of all of them. Unlike PostgreSQL, DB9 prints no NOTICE: truncate cascades to table … line for the extra tables it empties. The multi-table form TRUNCATE a, b and ONLY are rejected at parse time (42601).

TRUNCATE without CASCADE 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, and with RESTRICT as well. Use TRUNCATE parent CASCADE, or empty the referencing table first and remove the parent’s rows with DELETE — see Compatibility matrix.

CASCADE drops a child’s foreign key along with the parent

A dependent view or materialized view, and an inbound foreign key from another table, each block the drop with the same 2BP01 PostgreSQL raises:

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

DROP TABLE d1;
-- ERROR: cannot drop table d1 because other objects depend on it (2BP01)
-- DETAIL: view dv1 depends on table d1

DROP TABLE d1 CASCADE;   -- succeeds, and drops dv1 with it

CASCADE clears both kinds of dependent, but it does not treat them alike, and the difference is what to watch for:

A dependent view or materialized view is dropped outright.
A referencing foreign key is dropped, but the table carrying it is not. The child table and its rows survive — and so does every column — with only the constraint removed.

CREATE TABLE parent (id INT PRIMARY KEY);
CREATE TABLE child  (id INT PRIMARY KEY, pid INT REFERENCES parent(id));

DROP TABLE parent;
-- ERROR: cannot drop table parent because other objects depend on it (2BP01)

DROP TABLE parent CASCADE;            -- succeeds
SELECT * FROM child;                  -- rows intact
SELECT count(*) FROM pg_constraint;   -- works; child_pid_fkey is simply gone
INSERT INTO child VALUES (2, 99);     -- works — nothing validates pid any more

So CASCADE on a referenced table silently ends referential integrity for the child, which is the same trade PostgreSQL makes. Only the constraints whose parent went away are removed: a link table with foreign keys into two parents keeps the one whose parent is still there. List the inbound constraints before deciding:

SELECT conrelid::regclass::text AS from_table, conname
FROM pg_constraint
WHERE confrelid = 'public.parent'::regclass
  AND contype   = 'f';

Other DDL

Statement	Supported
`CREATE SCHEMA`	Yes
`ALTER SCHEMA`	`OWNER TO` only — `RENAME TO` is rejected (42601)
`CREATE SEQUENCE` / `DROP SEQUENCE`	Yes
`ALTER SEQUENCE`	Yes, except `SET SCHEMA`, `SET LOGGED` and `SET UNLOGGED` — 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 TYPE`	Enum values, `RENAME TO`, `RENAME ATTRIBUTE` and `OWNER TO` only — see below
`DROP TYPE`	Yes — `CASCADE` clears a function dependency but not a column one, and a `RETURNS TABLE (...)` column is not checked; see below
`CREATE FUNCTION` / `DROP FUNCTION`	Yes
`CREATE TRIGGER` / `DROP TRIGGER`	Yes
`CREATE COLLATION` / `DROP COLLATION`	Yes
`CREATE DATABASE` / `DROP DATABASE`	Yes
`ALTER DATABASE`	`RENAME TO`, `OWNER TO`, `SET` and `RESET` — see below
`CREATE EXTENSION` / `DROP EXTENSION`	Yes
`ALTER VIEW`	`OWNER TO` only — `RENAME TO` and `SET SCHEMA` are rejected (42601), as is every `ALTER MATERIALIZED VIEW` form
`COMMENT ON` (table, column, function, procedure, extension, index, schema)	Yes — indexes only when standalone, see below

COMMENT ON accepts seven object types, and only standalone indexes

TABLE, COLUMN, FUNCTION, PROCEDURE, EXTENSION, INDEX, and SCHEMA are supported. Every other target tested is rejected: VIEW, MATERIALIZED VIEW, SEQUENCE, TYPE, DATABASE, CONSTRAINT, TRIGGER, COLLATION, AGGREGATE, DOMAIN, POLICY, PUBLICATION, ROLE, RULE, OPERATOR, CAST, LANGUAGE, STATISTICS, TABLESPACE, SERVER, FOREIGN TABLE, LARGE OBJECT, ACCESS METHOD, and all four TEXT SEARCH targets (CONFIGURATION, DICTIONARY, PARSER, TEMPLATE).

INDEX is narrower than the row above suggests: it covers a standalone secondary index, one you created with CREATE INDEX or CREATE UNIQUE INDEX. The index behind a PRIMARY KEY or UNIQUE constraint is refused, since the comment would belong to the constraint rather than to the index:

CREATE TABLE t (id INT PRIMARY KEY, u TEXT UNIQUE, v TEXT);
CREATE INDEX t_v_idx ON t (v);

COMMENT ON INDEX t_v_idx IS 'lookup by v';   -- succeeds
COMMENT ON INDEX t_pkey  IS 'the key';
-- ERROR: COMMENT ON INDEX supports only ready standalone secondary indexes;
--        index "t_pkey" is not supported   (0A000)

t_u_key, the index behind the UNIQUE constraint, is refused the same way.

The rejections come in two kinds. VIEW, SEQUENCE, TYPE, DATABASE, DOMAIN, PUBLICATION, ROLE and TABLESPACE are refused with 0A000 and a message naming the object type, such as COMMENT ON VIEW is not supported (for TYPE and DOMAIN the message names the object too). The other targets in the list are not parsed at all and fail with 42601 (syntax error at or near …).

0A000 means the object type or index shape is unsupported — it does not mean your object is missing. For an unsupported type the check runs before the object is resolved, so 0A000 comes back even for a name that does not exist. When the type is supported and the object is not found, you get the usual “does not exist” error for that kind of object:

Missing object	SQLSTATE
`COLUMN`	`42703` undefined_column
`TABLE`, `INDEX`	`42P01` undefined_table
`SCHEMA`	`3F000` invalid_schema_name
`FUNCTION`, `PROCEDURE`	`42883` undefined_function
`EXTENSION`	`42704` undefined_object — `extension "vector" does not exist` for one that is not installed

Where the statement does succeed, the comment is written to pg_description, and OBJ_DESCRIPTION() / COL_DESCRIPTION() read it back for tables, columns, schemas, functions and procedures. Index and extension comments, and the one-argument OBJ_DESCRIPTION(oid) form, still read back as NULL — query pg_description directly for those. See description functions.

Sequence options: START, INCREMENT, MINVALUE, MAXVALUE, CACHE and CYCLE take effect in CREATE SEQUENCE, as in PostgreSQL — see Sequence Behavior for what CACHE does to the order and gaps of values across sessions. An identity column definition takes the same options, plus SEQUENCE NAME — see Identity columns above.

ALTER SEQUENCE cannot move a sequence to another schema

The parameter clauses take effect in place — RESTART and RESTART WITH, INCREMENT BY, START WITH, MINVALUE / NO MINVALUE, MAXVALUE / NO MAXVALUE, CACHE, CYCLE / NO CYCLE and AS, alone or several in one statement — and so do the ownership forms and RENAME TO (the statements below are alternatives, not a script to run top to bottom):

ALTER SEQUENCE order_seq RESTART WITH 5000;       -- next NEXTVAL returns 5000
ALTER SEQUENCE order_seq INCREMENT BY 10 MAXVALUE 100000;
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
ALTER SEQUENCE order_seq RENAME TO order_no_seq;  -- column defaults follow the new name

RENAME TO keeps the sequence’s position and rewrites any column default that calls it, so a SERIAL column keeps numbering from where it was. ALTER TABLE <sequence> RENAME TO is not a substitute — it is rejected with 42809. A new MAXVALUE is enforced from the next NEXTVAL (2200H, nextval: reached maximum value of sequence …), and the same statements work on the sequence behind a SERIAL or identity column.

SET SCHEMA, SET LOGGED and SET UNLOGGED are rejected with a bare message, where only the SQLSTATE is meaningful:

ERROR: internal error   (0A000)

A sequence’s schema is therefore fixed for its lifetime. To reposition a sequence without touching its parameters, SETVAL() works as well as RESTART WITH:

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

DROP SEQUENCE refuses while a column still depends on the sequence

A sequence that a column default calls cannot be dropped, and the table keeps working:

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

DROP SEQUENCE ref_s;
-- ERROR: cannot drop sequence ref_s because other objects depend on it (2BP01)
-- DETAIL: default value for column id of table ref_t depends on sequence ref_s

The sequence behind a SERIAL column is protected the same way. One behind an identity column is protected more strictly — cannot drop sequence ... because an identity column requires it (2BP01), with or without CASCADE.

DROP SEQUENCE ... CASCADE does drop a sequence used by a default, and it removes that column default along with it — silently. The table and its rows survive, but the next INSERT that relied on the default gets NULL for the column, which on a primary key fails with 23502.

There is no need to drop a sequence to change its parameters — ALTER SEQUENCE changes them in place, as shown above.

ALTER TYPE supports enum values, RENAME TO, RENAME ATTRIBUTE and OWNER TO; DROP TYPE misses RETURNS TABLE dependents

These forms work, and the change is visible in pg_type and pg_enum:

ALTER TYPE mood ADD VALUE 'excited';                -- also IF NOT EXISTS, BEFORE 'x', AFTER 'x'
ALTER TYPE mood RENAME VALUE 'sad' TO 'blue';       -- see enum arrays, below
ALTER TYPE mood RENAME TO feeling;
ALTER TYPE feeling OWNER TO app_user;               -- changes pg_type.typowner

RENAME ATTRIBUTE is accepted too, and the attribute name is validated — renaming one that does not exist fails with 42703:

ALTER TYPE addr RENAME ATTRIBUTE city TO town;    -- ALTER TYPE
ALTER TYPE addr RENAME ATTRIBUTE nope TO other;   -- ERROR: nope (42703)

There is no way to read the result back, though. A composite type has typrelid = 0 and no pg_attribute rows, information_schema.attributes does not exist, and field access such as (v).town fails under either name — so treat RENAME ATTRIBUTE as accepted and validated, not as something you can confirm from the catalog.

The remaining forms fail with 0A000 and a message naming the form — ALTER TYPE SET SCHEMA is not supported, and likewise for the attribute forms that would change the type’s shape: ADD ATTRIBUTE, DROP ATTRIBUTE, and ALTER ATTRIBUTE … TYPE. A composite type’s shape is therefore fixed once created; to change it, create a new type and migrate the columns that use it.

RENAME VALUE and enum arrays. RENAME VALUE updates the label in plain enum columns, but values inside an enum array column (mood[]) keep the old label. That has two consequences:

If one row holds the old label in both a plain column and an array column, the rename itself fails with 22P02 (invalid input value for enum mood: "sad") and nothing is changed.
After a rename that succeeds, an UPDATE to a row whose array still holds the old label fails with the same 22P02 — including one that only sets an unrelated column — unless it rewrites the array itself. DELETE still works.

Rewrite the affected arrays straight after the rename (array_replace() is not available):

UPDATE t SET moods = ARRAY(
  SELECT CASE WHEN x = 'sad' THEN 'blue' ELSE x END
  FROM unnest(moods::text[]) WITH ORDINALITY AS u(x, n) ORDER BY n
)::mood[]
WHERE 'sad' = ANY(moods::text[]);

DROP TYPE dependencies. A column of the type or of its array type (mood[]), or a function that takes it as an argument or returns it (RETURNS mood, RETURNS mood[], RETURNS SETOF mood), makes DROP TYPE fail with 2BP01, as in PostgreSQL. The plain DROP TYPE error carries HINT: Use DROP ... CASCADE to drop the dependent objects too, but CASCADE then behaves differently depending on which dependency it is:

Function argument or return type — CASCADE works as in PostgreSQL: the type is dropped and the dependent function goes with it (DROP TYPE succeeds, and the row disappears from both pg_type and pg_proc).
Column — CASCADE does not clear it, whether the column holds the type or an array of it. The statement still fails with 2BP01, and the DETAIL names the column and table that depend on the type. Drop or alter the column first.

One dependency is not checked: a RETURNS TABLE (...) column of the type. The drop succeeds without an error, and the function fails from then on — SELECT * FROM f() with 42704 (type "public.mood" does not exist). Look for such functions, and for columns, before dropping a type:

SELECT table_name, column_name FROM information_schema.columns WHERE udt_name IN ('mood', '_mood');
SELECT proname, pg_get_function_result(oid) FROM pg_proc WHERE pg_get_function_result(oid) ~ '\mmood\M';

(prorettype = 'mood'::regtype finds only the RETURNS mood and RETURNS SETOF mood functions — a function returning mood[] has a NULL prorettype.)

DROP TYPE on a name that does not exist fails with 42704 (type "…" does not exist), as in PostgreSQL; use DROP TYPE IF EXISTS when the type may be absent.

ALTER DATABASE accepts RENAME TO, OWNER TO, SET and RESET

All four work:

ALTER DATABASE reports RENAME TO reports_v2;   -- succeeds
ALTER DATABASE reports_v2 OWNER TO app_user;   -- succeeds (42704 if the role does not exist)

ALTER DATABASE reports_v2 SET search_path TO app, public;
ALTER DATABASE reports_v2 RESET search_path;
ALTER DATABASE reports_v2 RESET ALL;

SET stores the value in pg_db_role_setting with setrole = 0, and every session opened afterwards picks it up — a fresh connection reports search_path = app, public. RESET and RESET ALL remove the row again. db9.sequence_windows can be set only this way — see DB9-specific parameters. Other parameters, such as statement_timeout, work_mem or timezone, fail with 0A000 ALTER DATABASE configuration parameter "<name>" is not supported.

The remaining forms are rejected by the parser, before the database is even resolved:

ERROR: syntax error at or near "CONNECTION"   (42601)

That covers CONNECTION LIMIT, REFRESH COLLATION VERSION, IS_TEMPLATE, and ALLOW_CONNECTIONS; each error names the keyword the parser stopped at. SET TABLESPACE gets as far as SET and then fails at the tablespace name — ALTER DATABASE reports SET TABLESPACE fast_ssd reports syntax error at or near "fast_ssd". That is also 42601, but by a different path, so do not read its message as a hint that the name was wrong.

ALTER ROLE … SET is the per-role equivalent. It writes to the same pg_db_role_setting table, also surfaces in pg_roles.rolconfig, and every new session for that role picks it up, over both the wire protocol and the HTTP SQL API:

ALTER ROLE app_user SET search_path TO app, public;                     -- every database
ALTER ROLE app_user IN DATABASE postgres SET search_path TO app, public; -- this database only
ALTER ROLE app_user RESET search_path;                                  -- remove the default

Name the role explicitly. ALTER ROLE CURRENT_USER SET … and ALTER ROLE ALL SET …, which PostgreSQL accepts, fail here with 42704 (role "current_user" does not exist).

For a single connection, pass the parameter in the libpq options string with a space after -c — ?options=-c%20search_path%3Dapp in a connection URI, or PGOPTIONS="-c search_path=app". The compact spelling without the space (?options=-csearch_path%3Dapp), which PostgreSQL also accepts, is ignored without an error. A SET issued after connecting works as usual.

Row-Level Security DDL

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

ALTER TABLE ONLY t ... ROW LEVEL SECURITY is accepted for all four forms and does the same thing as the plain spelling — pg_class.relrowsecurity and relforcerowsecurity change as expected — so a pg_dump, which writes ALTER TABLE ONLY, restores unchanged. See Row-Level Security for complete policy semantics and examples.