Declaring Postgres Objects

A Postgres object is declared as its own DDL, inside a tagged template from @intentius/chant-lexicon-sql/postgres:

import { schema, sequence, table, index } from "@intentius/chant-lexicon-sql/postgres";

export const app = schema`CREATE SCHEMA app`;

export const ticketSeq = sequence`CREATE SEQUENCE ${app}.ticket_seq AS bigint START WITH 1000`;

export const tickets = table`
  CREATE TABLE ${app}.tickets (
    id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    no       bigint NOT NULL DEFAULT nextval(${ticketSeq}),
    title    text NOT NULL,
    priority int NOT NULL DEFAULT 3,
    CONSTRAINT tickets_priority_ck CHECK (priority BETWEEN 1 AND 5)
  );
  COMMENT ON TABLE ${app}.tickets IS 'Support tickets';
  COMMENT ON CONSTRAINT tickets_priority_ck ON ${app}.tickets IS 'One to five'`;

export const ticketsTitle = index`
  CREATE INDEX CONCURRENTLY tickets_title_idx ON ${tickets} (${tickets.columns.title})`;

chant build parses each template when it folds the file, without running it, into one entity. The export name (tickets) is the object’s identity in chant; the name in the SQL (app.tickets) is its name in the database. An unquoted name folds to lower case, as Postgres folds it, so CREATE TABLE Tickets is tickets, and .columns is keyed by the folded name. An object whose name has no schema is in the environment’s default schema, public unless the profile’s defaultSchema says otherwise.

The tags

Tag	Statement	Entity type
schema	CREATE SCHEMA	Postgres::Schema
table	CREATE TABLE, including PARTITION OF	Postgres::Table
index	CREATE [UNIQUE] INDEX [CONCURRENTLY] name ON ...	Postgres::Index
view	CREATE VIEW or CREATE MATERIALIZED VIEW	Postgres::View, Postgres::MaterializedView
sequence	CREATE SEQUENCE	Postgres::Sequence
type	CREATE TYPE name AS ENUM (...)	Postgres::Enum
domain	CREATE DOMAIN	Postgres::Domain
extension	CREATE EXTENSION	Postgres::Extension
func	CREATE [OR REPLACE] FUNCTION	Postgres::Function
procedure	CREATE [OR REPLACE] PROCEDURE	Postgres::Procedure
trigger	CREATE [OR REPLACE] [CONSTRAINT] TRIGGER	Postgres::Trigger
policy	CREATE POLICY	Postgres::Policy
role	CREATE ROLE, without a password or memberships	Postgres::Role
grant	GRANT, REVOKE, ALTER DEFAULT PRIVILEGES	Postgres::Grant, Postgres::DefaultPrivileges

A template holds one CREATE, of its tag’s kind, followed by any COMMENT ON statements for that object. A different statement, a second CREATE, or DDL that does not parse is SQLPG001, reported at the token as a line and column of the .ts file. The type tag declares enum types only; a composite or range type is refused at ( or RANGE.

A table’s template may also turn row-level security on after its CREATE TABLE (ALTER TABLE ... ENABLE ROW LEVEL SECURITY, FORCE ROW LEVEL SECURITY). Policies, roles, grants and row-level security are planned and applied only where the profile manages access; see Access.

Tables

A table’s declaration records every column (type, NOT NULL, default, GENERATED ... AS IDENTITY, GENERATED ALWAYS AS (...) STORED or VIRTUAL, collation, compression, storage) and every constraint: the primary key, unique constraints (with NULLS NOT DISTINCT, INCLUDE, WITH), checks (with NO INHERIT, NOT VALID, NOT ENFORCED), foreign keys (with MATCH, actions and deferral) and exclusion constraints. A constraint written on a column’s line is filed with the table’s, as Postgres files it. It also records PARTITION BY, PARTITION OF ... FOR VALUES, OF type, LIKE, INHERITS, USING, storage parameters in WITH (...), ON COMMIT and a tablespace.

A partition is a table of its own that names its parent by reference:

events.ts

import { index, schema, table } from "@intentius/chant-lexicon-sql/postgres";

export const telemetry = schema`CREATE SCHEMA telemetry`;

// A partitioned table's primary key has to include the partition key, so
// the key is (id, occurred_at). Rows route to the partition whose range holds
// occurred_at; a row outside every range goes to the default partition
// instead of failing the insert.
export const events = table`
  CREATE TABLE ${telemetry}.events (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    occurred_at timestamptz NOT NULL,
    device_id   bigint NOT NULL,
    kind        text NOT NULL,
    payload     jsonb NOT NULL DEFAULT '{}',
    PRIMARY KEY (id, occurred_at)
  ) PARTITION BY RANGE (occurred_at)`;

// One partition per month, created ahead of time. Ranges are half-open: the
// upper bound belongs to the next month.
export const eventsOct = table`
  CREATE TABLE ${telemetry}.events_2026_10 PARTITION OF ${events} FOR VALUES FROM ('2026-10-01') TO ('2026-11-01')`;

export const eventsNov = table`
  CREATE TABLE ${telemetry}.events_2026_11 PARTITION OF ${events} FOR VALUES FROM ('2026-11-01') TO ('2026-12-01')`;

export const eventsDec = table`
  CREATE TABLE ${telemetry}.events_2026_12 PARTITION OF ${events} FOR VALUES FROM ('2026-12-01') TO ('2027-01-01')`;

export const eventsDefault = table`
  CREATE TABLE ${telemetry}.events_default PARTITION OF ${events} DEFAULT`;

// An index on the parent exists on every partition, present and future.
export const eventsByDevice = index`
  CREATE INDEX events_by_device_idx ON ${events} (${events.columns.device_id}, ${events.columns.occurred_at} DESC)`;

REFERENCES ${users} (${users.columns.id}) makes the referenced table a dependency, so the build creates it first. A foreign key to a table the project does not declare can name it as text, REFERENCES billing.accounts (id); the build then cannot order the two.

What the build checks about a column: its type against the target major’s catalog in any spelling Postgres accepts, or against the types the project declares (SQLPG119), the type’s modifiers (SQLPG120), an identity column’s type (SQLPG121), that no column is declared twice (SQLPG122), the functions its DEFAULT or generated expression calls (SQLPG126), and, for a foreign key to a ${} table, that the referenced column exists and its type compares (SQLPG124). Column names in key, index and grant lists are checked against the table (SQLPG123); column names inside expressions are left to the server.

Name the constraints you expect to change. A constraint left unnamed gets a name from the server (orders_amount_check); a plan matches it by what it says rather than by that name, so it is not reported as changed, but a COMMENT ON CONSTRAINT needs the name in the declaration.

Indexes

An index needs a name, and the name is unqualified: an index lives in its table’s schema, and its name is its identity on the server. An unnamed index is SQLPG001.

export const ticketsOpen = index`
  CREATE INDEX CONCURRENTLY tickets_open_idx ON ${tickets} USING btree (${tickets.columns.priority})
  INCLUDE (${tickets.columns.title})
  WHERE ${tickets.columns.priority} > 3`;

The index records CONCURRENTLY, its method, its elements (with the column when an element is a plain column), INCLUDE and WHERE. Declare CONCURRENTLY on any index you add to a table that already holds rows: without it the build blocks every write to the table until it ends, and the plan reports SQLPG241. An index on a table the same plan creates is part of the create (SQLPG200), since an empty table has no writes to block; if it is declared CONCURRENTLY, the applier still builds it outside a transaction, once the table’s transaction has committed.

Views and materialized views

The view tag takes both. A view records its query, the objects its FROM and JOIN read (by reference), and the column lineage of its select list; see References and Lineage.

export const openTickets = view`
  CREATE VIEW ${app}.open_tickets WITH (security_invoker = true) AS
  SELECT t.${tickets.columns.id}, t.${tickets.columns.title}
  FROM ${tickets} t
  WHERE t.${tickets.columns.priority} > 3`;

In a join or with a table alias, qualify each column reference with the alias, t.${tickets.columns.id}: the reference renders as the column’s bare name.

Set security_invoker = true (Postgres 15 and later) unless the view should run with its owner’s privileges; SQLPG117 reports a view without it. A materialized view can end in WITH NO DATA, and needs a unique index over plain columns before REFRESH MATERIALIZED VIEW CONCURRENTLY works, which SQLPG111 checks; the RefreshedView composite declares both.

Sequences and regclass references

nextval(${seq}), currval(${seq}) and setval(${seq}, ...) render the sequence as a regclass literal, nextval('app.ticket_seq'::regclass), which is how pg_get_expr prints the default back. The declaration and the server’s catalog then read the same, and the table depends on the sequence. Any declared object followed by ::regclass renders the same way: pg_relation_size(${tickets}::regclass) reads pg_relation_size('app.tickets'::regclass).

A name typed in a string, nextval('app.ticket_seq'), records no reference, so the build may create the table before the sequence. SQLPG003 warns about it.

A sequence OWNED BY a column whose default calls nextval on that same sequence is a cycle, and the build fails naming it. An identity column, GENERATED ALWAYS AS IDENTITY, is the form to use there; it owns its sequence without declaring one, and SQLPG103 suggests it over serial.

Enum types, domains and extensions

import { domain, extension, type } from "@intentius/chant-lexicon-sql/postgres";

export const ticketStatus = type`CREATE TYPE ${app}.ticket_status AS ENUM ('open', 'closed')`;

export const email = domain`CREATE DOMAIN ${app}.email AS text CHECK (VALUE ~ '@')`;

export const trgm = extension`CREATE EXTENSION pg_trgm WITH SCHEMA ${app}`;

A column of an enum type or a domain names it by reference, status ${ticketStatus} NOT NULL DEFAULT 'open', so the build creates the type first. Labels are added to an enum in place (SQLPG260); removing or reordering them is expand and contract (SQLPG261).

An extension is checked against the configured managed provider’s list (SQLPG004, see Managed Providers) and against the target major’s catalog (SQLPG116 for one the major no longer ships). The objects an extension creates are its own, and chant neither declares nor imports them.

Functions, procedures and triggers

import { func, procedure, trigger } from "@intentius/chant-lexicon-sql/postgres";

export const touch = func`
  CREATE FUNCTION ${app}.touch() RETURNS trigger LANGUAGE plpgsql AS $$
  BEGIN
    NEW.updated_at := now();
    RETURN NEW;
  END
  $$`;

export const openCount = func`
  CREATE FUNCTION ${app}.open_count(min_priority int DEFAULT 1) RETURNS bigint
  LANGUAGE sql STABLE PARALLEL SAFE
  AS $$ SELECT count(*) FROM ${tickets} WHERE ${tickets.columns.priority} >= min_priority $$`;

export const closeOld = procedure`
  CREATE PROCEDURE ${app}.close_old(before timestamptz) LANGUAGE plpgsql AS $$
  BEGIN UPDATE ${tickets} SET status = 'closed' WHERE created_at < before; END
  $$`;

export const ticketsTouch = trigger`
  CREATE TRIGGER tickets_touch BEFORE UPDATE ON ${tickets}
  FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE FUNCTION ${touch}()`;

function is a JavaScript key word, so the tag is func. The body is a string constant (AS $$ ... $$ or AS '...'), kept as written: Postgres stores it verbatim and prints it back unchanged, so it is compared exactly. An interpolation inside the body renders as text, an object as its schema-qualified name and a column reference as the column’s name, and is recorded as a reference, so the build creates the routine after the table its body reads. A SQL-standard body (RETURN expr, BEGIN ATOMIC ... END) is refused, since the server keeps it parsed and prints it back rewritten; so is SET ... FROM CURRENT, which takes the value of whichever session runs the CREATE.

A routine’s identity on the server is its name and its input parameter types, so overloads are separate objects, and COMMENT ON FUNCTION ${app}.touch() IS '...' names it the same way. A trigger belongs to its table and its name is unqualified. A trigger’s arguments are stored as strings, and its WHEN condition is compared as the server prints it, its casts included.

Comments

Postgres has no inline comment clause, so a template follows its CREATE with COMMENT ON statements. They may name the object itself, one of its columns, or one of its named constraints, and nothing else:

COMMENT ON TABLE ${app}.tickets IS 'Support tickets';
COMMENT ON COLUMN ${app}.tickets.title IS 'What the reporter typed';
COMMENT ON CONSTRAINT tickets_priority_ck ON ${app}.tickets IS 'One to five'

They fold into the entity’s comment, each column’s and each constraint’s, and stay in the DDL the build writes. The applier sets the object’s own comment with chant’s ownership marker after it; see Applying to a Postgres Server. SQLPG112 asks for a comment on a column named like a secret or personal data (password, token, email, phone and similar), or on its table.

Interpolations

What an interpolation means is decided by its value:

Value	Renders as	Records
a Postgres entity (${tickets})	its schema-qualified name, app.tickets; a schema as its name	a reference
a column (${tickets.columns.title})	the column’s name, title	a reference to the column
a sequence in nextval, currval, setval, or any entity before ::regclass	a regclass literal, 'app.ticket_seq'::regclass	a reference
a string	SQL text, spliced in before the statement is parsed	nothing
a number or bigint	a numeric literal	nothing
true, false, null	true, false, NULL	nothing
literal(value)	a quoted string literal	nothing

Columns go through .columns

${tickets.title} is not a column. An entity’s own fields (kind, lexicon, entityType, props, sqlName, dependsOn) would splice their value in as SQL text, so ${tickets.kind} where a column is called kind builds into a different statement. SQLPG002 reports it for an entity the file declares or imports from a project file. Write ${tickets.columns.kind}.

A string is SQL text

A plain string is spliced into the statement as SQL, the way a composite’s columns prop is:

const auditColumns = "created_at timestamptz NOT NULL DEFAULT now(), created_by text";

export const notes = table`
  CREATE TABLE ${app}.notes (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ${auditColumns}
  )`;

When the string is a value, wrap it in literal(): DEFAULT ${literal("it's")} renders DEFAULT 'it''s'. literal() doubles a single quote and leaves a backslash as an ordinary character, as Postgres reads a string with standard_conforming_strings on, the default since 9.1.

Renames

The export name is the identity, so changing the name in the SQL under the same export is a rename of the object, which the plan treats as expand and contract (SQLPG228). A renamed column says so on its own line, and a renamed object before its CREATE:

-- previously: tickets
CREATE TABLE ${app}.issues (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL   -- previously: subject
)

See Postgres Locks and the Change Classifier for how a plan reads the hint, and Migrating a Column for running a column rename.

One dialect per build

A project’s build holds one dialect. Declaring a Postgres object and a ClickHouse object in the same project fails the build, naming one of each. Two databases of different dialects are two projects. sql.dialect: "postgres" in chant.config.ts marks a project as Postgres when nothing declared says so yet, such as before an import.

In the editor

Inside a template whose tag is imported from /postgres, chant serve lsp completes type names, index and table access methods after USING, storage parameters inside WITH ( for the object’s kind, extensions after CREATE EXTENSION, function names in expressions and key words, and offers the file’s declared objects after ${ and their columns after ${t.columns.. Hover shows what the catalog says about the name. Both read the catalog of the project’s major, sql.postgresMajor, so a name that major lacks is not offered; see The Postgres Pin and Supported Majors.

What the build records

Each entity’s parsed definition, its references and its lineage are in the build output; see Serialization. Lint Rules and Checks lists SQLPG001 to SQLPG004, which run over the source, and SQLPG101 to SQLPG126, which run over the build.