RFC DPG-1: Declarative PG

DPG Working Group                                          D. Tsegaw
Request for Comments: 1                                    Independent
Category: Standards Track                                  May 2026
ISSN: (N/A — project-internal specification)


         Declarative PG (DPG): A Declarative State-Based Superset
                        of PostgreSQL DDL

Abstract

   This document specifies Declarative PG (DPG), a declarative,
   state-based superset of PostgreSQL SQL that compiles to idiomatic
   PostgreSQL DDL.  DPG source files describe the desired state of a
   PostgreSQL database; the DPG compiler computes the minimal, safe,
   ordered set of DDL statements required to transition the current
   state to the desired state.  This specification defines the DPG
   source language, the compilation pipeline, the snapshot interchange
   format, the migration output format, and all associated tooling
   behaviour.

Status of This Memo

   This document specifies a Standards Track protocol for the DPG
   project ecosystem.  Distribution of this memo is unlimited.

   This document is the authoritative specification for DPG.
   Implementations MUST conform to every normative statement in this
   document.

Copyright Notice

   Copyright (c) 2026 Daniel Tsegaw.  All rights reserved.

   Redistribution and use of this specification, with or without
   modification, is permitted provided that the above copyright notice
   and this permission notice appear in all copies.

Table of Contents

1.  Introduction
    1.1.  Purpose and Scope
    1.2.  Problem Statement
    1.3.  Prior Art
    1.4.  Core Design Tenets
    1.5.  Terminology
2.  Conventions Used in This Document
    2.1.  Requirements Notation
    2.2.  Syntax Notation
    2.3.  Examples
3.  Project Structure and Configuration
    3.1.  Directory Layout
    3.2.  Root dpg.toml
    3.3.  Cluster dpg.toml
    3.4.  Database dpg.toml
    3.5.  Cluster-Level Objects Directory
    3.6.  Discovery Algorithm
    3.7.  Block Merge Conflict Resolution
    3.8.  Minimum PostgreSQL Version Targeting
    3.9.  Ignored Objects
4.  Language Fundamentals
    4.1.  Source File Format
    4.2.  The Two-Part Syntax Model
    4.3.  The No-Verb Mandate
    4.4.  Structural Scoping
    4.5.  Statement Terminators
    4.6.  Dollar-Quoted String Parsing
    4.7.  Macro Preprocessor
    4.8.  Dual Definition Modes
    4.9.  Block Merging
    4.10. Identifiers
5.  Type System
    5.1.  ENUM Types
    5.2.  Composite Types
    5.3.  Range Types
    5.4.  Domain Types
    5.5.  Base (Shell) Types
    5.6.  Virtual Types
6.  Schema and Namespace Objects
    6.1.  SCHEMA
    6.2.  EXTENSION
7.  Tables
    7.1.  Table Declaration Syntax
    7.2.  Column Definitions
    7.3.  Constraints
    7.4.  The COLUMN Reference Block
    7.5.  Column-Level Grants
    7.6.  Column Renaming
    7.7.  Indexes
    7.8.  Row Level Security
    7.9.  Triggers
    7.10. Table-Level Grants and Revocations
    7.11. Table Lifecycle Directives
    7.12. Unlogged and Foreign Tables
    7.13. Partitioned Tables
8.  Views
    8.1.  Regular Views
    8.2.  Materialized Views
    8.3.  Recursive Views
9.  Functions and Procedures
    9.1.  Function Declaration Syntax
    9.2.  Function Attributes
    9.3.  Procedures
    9.4.  Aggregate Functions
    9.5.  Function Body Diffing Semantics
10. Sequences
11. Access Control
    11.1.  Roles
    11.2.  Grants — The Additive Model
    11.3.  Revocations
    11.4.  Default Privileges
    11.5.  Owner Impersonation at Object Creation
    11.6.  Parameter Privileges
12. Full-Text Search Objects
    12.1.  Text Search Configurations
    12.2.  Text Search Dictionaries
    12.3.  Text Search Parsers
    12.4.  Text Search Templates
13. Logical Replication
    13.1.  Publications
    13.2.  Subscriptions
14. Advanced PostgreSQL Objects
    14.1.  Event Triggers
    14.2.  Collations
    14.3.  Operators
    14.4.  Operator Classes and Families
    14.5.  Casts
    14.6.  Extended Statistics Objects
    14.7.  Tablespaces
    14.8.  Foreign Data Wrappers
    14.9.  Foreign Servers
    14.10. User Mappings
    14.11. Security Labels
15. Compilation Pipeline
    15.1.  Phases Overview
    15.2.  Phase 1 — File Discovery
    15.3.  Phase 2 — Macro Preprocessing
    15.4.  Phase 3 — Tokenization
    15.5.  Phase 4a — PostgreSQL Parsing (Part 1)
    15.6.  Phase 4b — Block Parsing (Part 2)
    15.7.  Phase 5 — IR Construction
    15.8.  Phase 6 — Merging
    15.9.  Phase 7 — Dependency Resolution
    15.10. Phase 8 — Linting
    15.11. Phase 9 — Differencing
    15.12. Phase 10 — Emission
16. Snapshot Format
    16.1.  Purpose and Placement
    16.2.  Top-Level Fields
    16.3.  Per-Object Snapshot Schema
    16.4.  Versioning
    16.5.  Further Object Records
    16.6.  Cluster-Level Snapshot File
    16.7.  Source Revision Detection
    16.8.  Migration Log
    16.9.  Ledger
    16.10. Preconditions
17. Migration Output Format
    17.1.  Output Structure
    17.2.  Safety Classification
    17.3.  Execution Modes
    17.4.  Idempotency Requirement
18. CLI Commands
    18.1.  dpg plan
    18.2.  dpg apply
    18.3.  dpg verify
    18.4.  dpg resolve
    18.5.  dpg dump
    18.6.  dpg diff
    18.7.  dpg validate
    18.8.  dpg fmt
    18.9.  dpg portability
    18.10. dpg init
    18.11. dpg completion
    18.12. dpg docs
    18.13. dpg log
    18.14. Target Selection
19. The Linter
    19.1.  Built-in Rules
    19.2.  Configuration
    19.3.  Rule Scope
20. Introspection Engine
    20.1.  Catalog Tables Read
    20.2.  Drift Detection
21. Per-Object Diff Algorithms
22. Dependency Ordering
    22.1.  Topological Sort
    22.2.  Circular Dependency Resolution
23. Out of Scope
24. Security Considerations
25. Feature Coverage Matrix
Appendix A.  ABNF Grammar Summary
Appendix B.  Complete Example Project
Appendix C.  Error Code Reference
Appendix D.  Supplementary Specifications
    D.1.  Secret Resolution
    D.2.  Name Maps
    D.3.  SERIAL / BIGSERIAL / SMALLSERIAL Columns
Appendix E.  Revision History
Appendix F.  Standard SQL / PostgreSQL-Specific Classification
Normative References
Informative References
Author's Address

1. Introduction

1.1. Purpose and Scope

This document specifies the Declarative PG (DPG) language, its compilation model, and all associated tooling. DPG is a declarative, state-based superset of PostgreSQL SQL. A DPG source tree describes the desired state of one or more PostgreSQL databases. The DPG compiler ingests that description, compares it against a committed schema snapshot, and emits the minimal ordered set of PostgreSQL DDL statements required to transition the database to the desired state.

The scope of this specification covers:

a) The DPG source language syntax, including all object declaration forms, the two-part syntax model, the macro preprocessor, and all lifecycle directives.

b) Every category of PostgreSQL object that DPG can manage, including the precise DDL each declaration produces.

c) The full compilation pipeline from file discovery through DDL emission, including intermediate representations.

d) The snapshot interchange format and its versioning contract.

e) The migration output format, safety classification system, and idempotency guarantees.

f) All CLI commands, their flags, and their observable behaviour.

g) The static analysis linter and its built-in rules.

h) The live catalog introspection engine and drift detection.

This specification does NOT cover:

• Any implementation’s internal data structures beyond what is required to define observable behaviour.
• PostgreSQL runtime behaviour (query planning, execution, etc.).
• Data manipulation language (SELECT, INSERT, UPDATE, DELETE), except the statements a MIGRATE REMOVE block supplies (Section 5.1.2).
• Inline data seeding (out of scope; DPG is a schema tool).

1.2. Problem Statement

PostgreSQL DDL is fundamentally imperative: to change a database one issues commands (CREATE TABLE, ALTER TABLE ADD COLUMN, DROP INDEX). SQL files in version control therefore describe actions taken at a point in time, not the current intended state of the schema. This creates four well-known failure modes:

(1) Schema drift. A production database that has been patched, hotfixed, or manually altered over time no longer matches its migration history. There is no reliable way to tell whether a given migration file has been applied.

(2) No single source of truth. To understand the current schema a developer must mentally replay every migration, in order, from the beginning. This is error-prone and does not scale.

(3) Idempotency is illusory. Running a migration file twice fails or corrupts state. Most teams work around this with IF NOT EXISTS guards that must be written manually.

(4) Redundant context. PostgreSQL forces re-statement of context that is already structurally known. ALTER TABLE public.users ADD CONSTRAINT ... repeats schema and table name in every alteration even when the context is fixed.

DPG resolves all four problems by inverting the model: the developer writes a description of the desired state; the compiler produces the imperative commands.

1.3. Prior Art

Migration-history tools (Flyway, Liquibase, Sqitch, and the migration systems of most ORMs) store the sequence of imperative changes, not the desired state. The current schema exists only as the replay of every migration.

Tools with their own schema language (Atlas HCL, Prisma Schema) describe the desired state in a vocabulary that is not SQL, so objects PostgreSQL already names have to be relearned, and PostgreSQL features the vocabulary does not model cannot be expressed.

Plain-SQL desired-state tools (psqldef, migra, pg-schema-diff, Atlas’s SQL schema mode, Supabase declarative schemas) take a set of CREATE statements as the desired state and diff it against a database. This keeps SQL, but three gaps remain:

1. Properties PostgreSQL only sets through separate statements (owner, grants, comments, row level security, column statistics, replica identity) have no declarative home. They are written as ALTER/GRANT/COMMENT statements, which the tool must then interpret as state.
2. Intent that the end state cannot show (a rename, a deprecation, protection from drops, migrating rows off an enum value) cannot be expressed, so the tool guesses or drops and recreates.
3. Several compute the diff by loading the SQL into a scratch database, so planning needs a running PostgreSQL.

The DPG position: PostgreSQL’s CREATE statements already describe objects well. DPG keeps them as written, minus the verb, and adds the three missing pieces: a { } block that holds the properties PostgreSQL sets separately, lifecycle directives for intent, and an offline diff against a committed snapshot.

1.4. Core Design Tenets

Tenet 1 — Full PostgreSQL feature parity. DPG MUST be capable of expressing anything that raw PostgreSQL DDL can express. A PostgreSQL feature that cannot be declared in DPG is a defect in DPG, not an out-of-scope request — with the sole exception of the specific, itemized carve-outs enumerated in Section 23 (“Out of Scope”), each justified there on its own terms (e.g. a feature that is runtime/session-scoped rather than schema DDL, or genuinely superseded by another PostgreSQL mechanism DPG already manages). Section 23’s list is closed, not a general license to omit — anything not named there is bound by this tenet without exception.

Tenet 2 — Prefer PG syntax exactly. When PostgreSQL already has a declarative way to express something, DPG uses it verbatim. DPG removes imperative verbs and adds structural scoping but does not invent new keywords for concepts PostgreSQL already names well.

Tenet 3 — Standard SQL / PG-extension boundary is tracked internally. The compiler knows which constructs are ISO/IEC 9075 Standard SQL and which are PostgreSQL-specific. Users never annotate portability. The compiler surfaces this via the dpg portability command. The full per-construct classification is Appendix F.

PostgreSQL versions: this version of the specification covers PostgreSQL 14 through 18. A construct requiring a version after 14 is marked as such where it is specified and in Appendix F. Constructs introduced by later PostgreSQL releases are added by amendments to this specification (see Versioning, below). This range is distinct from the per-project min_pg_version setting (Section 3.8), which declares the oldest server a particular project deploys to.

Versioning: this specification is versioned MAJOR.MINOR.PATCH. An amendment that only adds (support for a new PostgreSQL release’s constructs, a new optional directive, a new error code) increases MINOR. An amendment that changes the meaning of valid source, or removes or renames a keyword, increases MAJOR. Editorial corrections that change no requirement increase PATCH.

Tenet 4 — Offline-first diffing. DPG MUST NOT require a live database connection to generate a migration. The primary workflow compares .dpg source files against a committed schema snapshot. Live catalog introspection is available for verification and bootstrap but is never required for day-to-day operation.

Tenet 5 — The { } block holds only what PG SQL cannot. The native PostgreSQL DDL definition of an object — its column list, its options, its clauses, its dollar-quoted body — MUST be written exactly as PostgreSQL SQL dictates. The trailing { } block exists exclusively for things PostgreSQL SQL expresses as separate DDL statements (indexes, grants, policies, comments, per-column storage attributes) and for DPG lifecycle directives (RENAMED FROM, PROTECTED, DEPRECATED). Nothing that has a natural place in PostgreSQL SQL’s own syntax SHALL be moved into the { } block.

1.5. Terminology

The following terms carry precise meanings throughout this document:

DPG source file — A UTF-8 text file with the .dpg extension containing one or more DPG object declarations.

Part 1 — The native PostgreSQL SQL portion of a DPG declaration, written with the leading imperative verb removed.

Part 2 — The optional trailing { } block of a DPG declaration, holding sub-objects and lifecycle directives.

Object — A named, independently manageable PostgreSQL schema entity (table, view, function, type, role, etc.).

Snapshot — A committed JSON file representing the compiler’s normalised view of the database state after the most recent successful dpg apply.

IR (Internal Representation) — The fully-qualified, typed in-memory form of a DPG object, produced by the IR Builder phase.

DiffOp — A single DDL statement produced by the Differ, annotated with safety class and source position.

Migration — The complete ordered output of the Emitter: a set of transactional DiffOps and a set of non-transactional DiffOps, together with a header block.

Safety class — One of SAFE, CAUTION, or DESTRUCTIVE. Defined in Section 17.2.

Execution mode — One of transactional, non-transactional, or instruction-only. Independent of the safety class. Defined in Section 17.3.

Cluster — A single running PostgreSQL instance, hosting one or more databases. Maps to one cluster directory in the project tree.

Database — A single named PostgreSQL database within a cluster. Maps to one database directory in the project tree.

2. Conventions Used in This Document

2.1. Requirements Notation

The key words “MUST”, “MUST NOT”, “REQUIRED”, “SHALL”, “SHALL NOT”, “SHOULD”, “SHOULD NOT”, “RECOMMENDED”, “NOT RECOMMENDED”, “MAY”, and “OPTIONAL” in this document are to be interpreted as described in BCP 14 [RFC2119] [RFC8174] when, and only when, they appear in all capitals, as shown here.

2.2. Syntax Notation

ABNF grammar rules are specified using the notation defined in [RFC5234], Augmented BNF for Syntax Specifications. The following core rules from [RFC5234] Appendix B are used without redefinition: ALPHA, DIGIT, SP, HTAB, CRLF, LF, DQUOTE.

Where existing PostgreSQL DDL syntax is referenced, it is cited in terms of the PostgreSQL 14+ CREATE statement grammar as documented in the PostgreSQL official documentation [PGDOC14].

Inline examples appear in monospace code blocks. Within prose, DPG keywords appear in monospace. Non-terminal grammar symbols appear in italics. Normative text and examples are separated; an example does not in itself constitute a normative requirement unless explicitly stated.

2.3. Examples

Examples marked -- OK illustrate valid DPG source. Examples marked -- ERROR illustrate input that MUST be rejected by the compiler with a diagnostic. Example PostgreSQL DDL output is marked -- emitted SQL.

3. Project Structure and Configuration

3.1. Directory Layout

[DPG] A DPG project is a directory tree. Three kinds of dpg.toml file mark its structure, each declaring its kind on a top-level kind key:

• kind = "root": the project root. Exactly one, at the top of the tree.
• kind = "cluster": a cluster directory (one PostgreSQL server, Section 1.5).
• kind = "database": a database directory (one database within a cluster).

Cluster and database directories MAY sit at any depth below the root. A typical layout:

<project-root>/
├── dpg.toml                        kind = "root"
│
├── <cluster>/                      (any depth below the root)
│   ├── dpg.toml                    kind = "cluster"
│   ├── <cluster-objects-dir>/      (default: "cluster")
│   │   ├── roles.dpg
│   │   └── tablespaces.dpg
│   │
│   └── <database>/                 (any depth below its cluster)
│       ├── dpg.toml                kind = "database"
│       ├── extensions.dpg
│       └── schemas/
│           └── <schema>/
│               ├── schema.dpg      (optional: the schema's own directives)
│               ├── types.dpg
│               ├── tables/
│               │   └── <table>.dpg
│               ├── views.dpg
│               └── functions.dpg
│
└── .dpg/
    ├── snapshots/<cluster>/<database>.json
    └── migrations/<cluster>/<database>/

Structure rules:

1. The project root is the directory whose dpg.toml has kind = "root". A dpg.toml with no kind, or with any other value, is error DPG-E041; so is kind = "root" anywhere below the root.

2. A database belongs to one cluster: the cluster directory named by its parent key (Section 3.4) when set, otherwise its nearest ancestor directory with kind = "cluster". A database with neither is error DPG-E042. A database therefore MAY live outside its cluster’s directory, for example in a shared databases/ folder, by naming the cluster with parent.

3. A cluster directory MUST NOT be inside another cluster or database directory, and a database directory MUST NOT be inside another database directory (error DPG-E043).

4. All .dpg files at any depth under a database directory belong to that database. No in-file database header keyword exists.

5. .dpg files under a cluster’s objects directory (Section 3.5) belong to the cluster, with no database context.

6. Files whose names do not end in .dpg, and directories whose names begin with ., are ignored.

7. [DPG] .dpg files under <database>/schemas/<name>/, at any depth, are scoped to schema <name>: an unqualified declaration there belongs to that schema, exactly as if it were written inside SCHEMA <name> { }. The directory name is used verbatim as the schema name, with no case folding. A file in that directory MAY declare SCHEMA <name> { ... } to hold the schema’s own directives (OWNER, COMMENT, GRANTS/REVOKES, default privileges, RENAMED FROM); by convention this is schemas/<name>/schema.dpg. Declaring any other schema there is error DPG-E040. The schema itself exists in the desired state whenever its directory contains at least one .dpg file.

Reason for typed configs: a directory’s role is stated, not guessed from which sections its dpg.toml happens to contain, so a project can be organised to any depth and a misplaced file is reported instead of silently changing the project’s structure.

3.2. Root dpg.toml

The root dpg.toml configures global compiler and linter behaviour. kind is REQUIRED; every other field is OPTIONAL and takes the default shown.

kind = "root"

[compiler]
# default_drop_behavior controls whether DROP statements include
# CASCADE or RESTRICT. Valid values: "restrict" (default), "cascade".
# Per-object DROP CASCADE overrides this setting.
default_drop_behavior = "restrict"

# Minimum PostgreSQL major version this project targets (Section 3.8).
# Unset (the default) means no version gating. Overridable per-cluster
# and per-database (Sections 3.3/3.4); most specific wins.
# min_pg_version = 15

[linter]
# Emit a warning when any DEPRECATED object or column is referenced.
warn_on_deprecated = true

# Emit an error when any column lacks a COMMENT.
require_column_comments = false

# Emit an error when a ROLE PASSWORD value is a literal rather than a
# {{secret-uri}} placeholder.
forbid_hardcoded_passwords = true

# Emit a warning when a table has more than this many columns.
# 0 = disabled.
max_columns_per_table = 50

[snapshots]
# Directory where snapshot JSON files are stored, relative to the
# project root.
directory = ".dpg/snapshots"

[fmt]
# Number of spaces per indentation level. Default: 4.
indent = 4

# Keyword casing applied to all DPG and PostgreSQL keywords.
# Valid values: "upper" (default), "lower".
keyword_case = "upper"

[migrations]
# Relative path from the project root of the migration log
# (Section 16.8). Default: ".dpg/migrations". It can't be empty.
directory = ".dpg/migrations"

[ledger]
# The table each database records the migrations it has run in
# (Section 16.9). Default: true.
enabled = true
# Its schema. Default: "dpg".
schema = "dpg"

[ignore]
# Objects that exist in a database but are not managed by DPG
# (Section 3.9). OPTIONAL; allowed in every dpg.toml.
# extensions = ["pg_stat_statements"]

The compiler MUST reject any key in any dpg.toml that is not listed in this section, Section 3.3, or Section 3.4 for that file’s kind, with error DPG-E001 (unknown configuration key).

[fmt] controls dpg fmt (Section 18.8). [migrations] names the directory of the migration log (Section 16.8), which MUST be committed to version control with the snapshots.

3.3. Cluster dpg.toml

Located in a cluster directory. Configures the cluster’s name and connection.

kind = "cluster"

[cluster]
# Human-readable name. Used in snapshot file paths and migration
# headers. REQUIRED.
name = "production"

# Directory, relative to this cluster directory, holding cluster-level
# objects (Section 3.5).
cluster_objects_dir = "cluster"   # default

# Inline PostgreSQL connection string (libpq URI or keyword/value
# format). Mutually exclusive with `link`. OPTIONAL.
url = "postgresql://user@host:5432/postgres"

# Secrets-provider URI resolved at connection time. Supported schemes
# (see Appendix D.1): env:<VAR>, vault:<mount>/<path>#<field>, aws-sm:<secret-id>,
# gcp-sm:<project>/<secret-id>, azure-kv:<vault-name>/<secret-name>.
# Mutually exclusive with `url`. OPTIONAL.
# link = "env:PRIMARY_DB_URL"

[compiler]
# Overrides the project root's min_pg_version (Section 3.2) for
# every database in this cluster, and for this cluster's own
# cluster-level objects (roles, tablespaces, PARAMETER PRIVILEGES).
# OPTIONAL; unset falls through to the root's value. See Section 3.8.
# min_pg_version = 16

[ignore]
# Objects this environment has that DPG does not manage, such as a
# hosting platform's roles (Section 3.9). OPTIONAL.
# roles = ["rdsadmin", "rds_*"]

[DPG] Connections. url and link are both OPTIONAL: they are needed only by commands that connect (dpg apply, dpg verify, dpg dump, dpg plan --live). The cluster connection is used:

• as written, for cluster-level objects (Section 3.5); its own database (for example postgres) is the database those commands connect to;
• for each database that sets no connection of its own (Section 3.4), with the connection’s database name replaced by that database’s name.

Constraints: url and link are mutually exclusive (error DPG-E002). A command that needs a connection and finds none for its target fails with error DPG-E003. name is required (error DPG-E032) and MUST be unique across every cluster in the project (error DPG-E034, naming both directories).

3.4. Database dpg.toml

Located in a database directory.

kind = "database"

# Path, relative to this directory, of the cluster directory this
# database belongs to. OPTIONAL; default: the nearest ancestor
# directory whose dpg.toml has kind = "cluster" (Section 3.1).
# parent = "../../clusters/production"

[database]
# The name of the PostgreSQL database as it appears in pg_database.
# REQUIRED.
name = "myapp"

# The schema for objects declared with no schema qualifier outside any
# schema scope (Section 4.4). OPTIONAL; default "public".
default_schema = "public"

# This database's own connection, replacing the cluster's for this
# database only. Used verbatim: the database name in it is not
# rewritten. Same forms as the cluster's url/link (Section 3.3).
# Mutually exclusive. OPTIONAL.
# url  = "postgresql://myapp_owner@pgbouncer:6432/myapp_pool"
# link = "env:MYAPP_DB_URL"

[compiler]
# Overrides the cluster's (and, transitively, the project root's)
# min_pg_version (Sections 3.2/3.3) for this database only. OPTIONAL;
# unset falls through to the cluster's value. See Section 3.8.
# min_pg_version = 17

[ignore]
# Objects in this database that DPG does not manage (Section 3.9).
# OPTIONAL.
# schemas = ["cron"]

[DPG] parent MUST resolve inside the project root to a directory whose dpg.toml has kind = "cluster". When the database directory is itself inside a cluster directory, parent MAY only name that same cluster. Any other value is error DPG-E042.

[DPG] A database-level url/link serves cases a single cluster connection cannot: a different role per database, or a connection pooler whose pool name differs from the database name.

Constraints: url and link are mutually exclusive (error DPG-E002). name is required (error DPG-E033) and MUST be unique among the databases of the same cluster (error DPG-E035, naming both directories); the same name MAY recur under a different cluster.

3.5. Cluster-Level Objects Directory

Files in the cluster objects directory declare objects that belong to the cluster, not to any individual database: roles, tablespaces, parameter privileges, and (in the rare case of custom C-implemented FDWs) foreign data wrappers. The directory is <cluster-dir>/<cluster_objects_dir>. A database directory at or below it is error DPG-E004 (reserved name conflict).

3.6. Discovery Algorithm

File discovery MUST produce the result of the following steps:

1. Locate the project root: starting at the current working directory and moving upward, the first directory whose dpg.toml has kind = "root". A dpg.toml met on the way with no kind is error DPG-E041.

2. Walk the whole tree below the root, skipping directories whose names begin with ., and read the kind of every dpg.toml found (Section 3.1, rule 1).

3. Load every cluster (Section 3.3), checking rule 3 of Section 3.1.

4. Load every database (Section 3.4), checking rule 3, and attach it to its cluster (rule 2).

5. For each database, collect every .dpg file under its directory; for each cluster, collect every .dpg file under its objects directory. The compiled result never depends on the order of these files (Section 3.7).

6. Validate names: cluster names non-empty and unique project-wide (DPG-E032, DPG-E034); database names non-empty and unique within their cluster (DPG-E033, DPG-E035). Reason: --cluster and --database selection (Section 18.14), snapshot files, the migrations archive, and dpg dump’s output path are all keyed by name, so an empty or repeated name would make one target unreachable or make two targets share files.

3.7. Block Merge Conflict Resolution

[DPG] All declarations across all .dpg files for the same logical database are compiled together. When the same named object is declared in more than one file, the declarations are merged:

Set-valued properties — columns, constraints, indexes, policies, triggers, grants, revokes, partitions, role memberships, enum values — are merged by taking the union of all declared entries. Identical entries (same name and same definition) are deduplicated. Two entries with the same name but different definitions are error DPG-E005 (conflicting set member).

Scalar properties — every other property: owner, comment, tablespace, RENAMED FROM, DEPRECATED, a column’s type or default, a sequence option, and so on — may be declared in any number of the files, but every declaration MUST give the same value. A property declared in only one file is taken from that file. Two different values for the same property are error DPG-E039 (conflicting scalar property); the error names both files. Option lists (OPTIONS, WITH (...)) are compared key by key, so files may set different keys. Presence flags (PROTECTED, DROP CASCADE, ENABLE ROW LEVEL SECURITY, …) are set if any declaration sets them.

Object kinds with no per-property merge (publications, event triggers, casts, operators, text search objects, and similar) MAY be declared in more than one file only if every declaration is identical; otherwise the compiler reports DPG-E039.

Reason: the compiled result must not depend on file names, file order, or how source is split across files.

3.8. Minimum PostgreSQL Version Targeting

min_pg_version ([compiler], Sections 3.2-3.4) declares the oldest PostgreSQL major version a project — or one of its clusters or databases — is deployed against. This is a purely semantic gate, not a parser restriction: the compiler always parses .dpg source against its newest supported grammar, since PostgreSQL’s own SQL grammar is overwhelmingly additive across major versions and a project’s source is not expected to vary its own syntax by target version. What min_pg_version controls is whether the linter (Section 19) warns — or, under --strict, errors — when a construct the compiler did successfully parse requires a PostgreSQL version newer than the effective floor. This lets a project declare “PARAMETER PRIVILEGES on a PG14 target” and be told about the mismatch before dpg apply fails against a real, older server, without DPG needing a distinct parser build per PostgreSQL release.

Resolution order: min_pg_version MAY be set at the project root (Section 3.2), overridden per-cluster (Section 3.3), and overridden again per-database (Section 3.4). The most specific level that sets it wins; an unset level falls through to the next level up. Unset at every level means no gating at all. A cluster’s own cluster-level objects (roles, tablespaces, PARAMETER PRIVILEGES) are gated by that cluster’s own resolved value, since they have no database of their own to resolve one from.

Validation: a declared min_pg_version below 14 (this specification’s own supported floor, Section 1.4) MUST be rejected at config-load time as an error, at whichever level declared it.

Enforcement: implemented as the linter’s min-pg-version rule (Section 19.1, Section 19.3) — a LintDiagnostic warning by default, promoted to an error under --strict like any other rule, wired into dpg plan/dpg apply/dpg validate (Section 18) the same way every other linter rule already is; no separate CLI flag or command-specific wiring is needed. Every version-gated construct this specification documents is catalogued in Appendix F’s Min PG Version column — a blank cell there means the construct has been available since this specification’s own floor (PostgreSQL 14) and is never gated, regardless of min_pg_version.

Coverage: the rule MUST check every declared construct that has a value in Appendix F’s Min PG Version column, except rows marked (on change). Those are statements DPG emits only to apply a particular change to an object that already exists (for example ALTER COLUMN ... SET EXPRESSION); nothing in the source shows them, so PostgreSQL itself rejects them when the migration runs against an older server. SQL inside expressions and bodies (view queries, function bodies, DEFAULT and CHECK expressions) is not checked.

3.9. Ignored Objects

[DPG] The [ignore] section of a dpg.toml lists name patterns of objects that exist in a database but are not managed by DPG, such as the roles, schemas and extensions a hosting platform creates. The lists of the project root, the cluster and the database apply together, so patterns for one environment belong in that cluster’s dpg.toml. The ledger’s schema (Section 16.9) is always ignored.

[ignore]
roles      = ["rdsadmin", "rds_*"]
schemas    = ["cron"]
extensions = ["pg_stat_statements"]
functions  = ["public.pgaudit_*"]

Each key names an object kind; its value is a list of patterns, in which * matches any run of characters and ? any one character. For a schema-scoped kind, a pattern containing . matches schema.name and any other pattern matches the name in every schema. An object in an ignored schema is ignored too.

Keys: roles, tablespaces, schemas, extensions, tables (including foreign tables), views (including recursive and materialized views), functions, procedures, aggregates, types (enums, composites, ranges, domains and base types), sequences, collations, operators, operator_classes, operator_families, casts (named source->target), statistics, foreign_data_wrappers, servers, user_mappings (named user@server), publications, subscriptions, event_triggers, text_search_configurations, text_search_dictionaries, text_search_parsers, text_search_templates. Any other key is error DPG-E001; a malformed pattern is a configuration error.

An ignored object is removed from the live catalog and from the snapshot before every comparison (dpg plan, dpg apply, dpg verify), and dpg dump does not write it. Declaring an object that matches a pattern is error DPG-E045. Predefined roles (pg_*) and the bootstrap superuser are never read from the catalog (Section 20.1) and need no pattern.

4. Language Fundamentals

4.1. Source File Format

DPG source files MUST be encoded in UTF-8 [RFC3629]. Byte-order marks (U+FEFF) MUST be silently stripped if present at the start of a file. Line endings MAY be LF (U+000A) or CRLF (U+000D U+000A); the compiler MUST normalise all line endings to LF before processing.

Comments follow PostgreSQL’s double-dash convention (--) and C- style block comments (/* ... */). Comments are stripped by the tokenizer before any deeper parsing. Block comments do NOT nest.

Identifiers are case-insensitive in conformance with PostgreSQL’s unquoted identifier rules, except when enclosed in double-quotes, in which case they are case-sensitive and may contain any Unicode character.

4.2. The Two-Part Syntax Model

Every DPG object declaration consists of at most two parts:

Part 1 — The native PG SQL definition. Written exactly as PostgreSQL SQL dictates with only the leading imperative verb (CREATE, ALTER, DROP) removed. Part 1 uses the same keywords, the same clause ordering, and the same syntax as the corresponding PostgreSQL CREATE statement. The compiler prepends the correct verb internally when invoking the PostgreSQL parser; the developer never writes it.

Part 2 — The DPG structural block { ... }. An OPTIONAL trailing block that contains exclusively things PostgreSQL SQL expresses as separate DDL statements (CREATE INDEX, GRANT, COMMENT ON, ALTER TABLE ... ALTER COLUMN SET STATISTICS) plus DPG lifecycle directives (RENAMED FROM, PROTECTED, DEPRECATED, DROP CASCADE).

Decision rule: If PostgreSQL writes it as part of CREATE OBJECT, it is Part 1. If PostgreSQL writes it as a separate statement, it is Part 2. This rule MUST be applied consistently; no clause that belongs in Part 1 SHALL be moved to Part 2, and no Part-2-only directive SHALL appear in Part 1.

dpg-declaration  = object-keyword WSP object-body terminator
                 ; object-body is the PG SQL text with CREATE verb
                 ; absent; terminator is defined in Section 4.5

part2            = "{" *( WSP* block-item ) WSP* "}"
block-item       = block-directive WSP* ";"     ; e.g. COMMENT '...';
                 / block-collection              ; e.g. GRANTS { ... }
                 ; a block-collection ends at its own "}"; a ";" after
                 ; it is permitted and never required

object-keyword   = "SCHEMA" / "TABLE" / "UNLOGGED TABLE" /
                   "FOREIGN TABLE" / "VIEW" / "MATERIALIZED VIEW" /
                   "RECURSIVE VIEW" / "FUNCTION" / "PROCEDURE" /
                   "AGGREGATE" / "TYPE" / "DOMAIN" /
                   "SEQUENCE" / "ROLE" / "TABLESPACE" /
                   "FOREIGN DATA WRAPPER" / "SERVER" / "USER MAPPING" /
                   "PUBLICATION" / "SUBSCRIPTION" / "EVENT TRIGGER" /
                   "COLLATION" / "OPERATOR" / "OPERATOR CLASS" /
                   "OPERATOR FAMILY" / "CAST" / "STATISTICS" /
                   "TEXT SEARCH CONFIGURATION" /
                   "TEXT SEARCH DICTIONARY" /
                   "TEXT SEARCH PARSER" / "TEXT SEARCH TEMPLATE" /
                   "DEFAULT PRIVILEGES" / "PARAMETER PRIVILEGES" /
                   "VIRTUAL TYPE" / "EXTENSION" / "MACRO"

4.2.1. Common Part 2 Directives

These directives have the same meaning in every { } block that accepts them. Each object section lists which ones it accepts. A directive is tagged [PG: ...] when it declares state that PostgreSQL sets with the separate statement named, and [DPG] when PostgreSQL has no such state (it is information for the compiler).

owner-dir        = "OWNER" WSP identifier
comment-dir      = "COMMENT" WSP string-literal
renamed-from-dir = "RENAMED FROM" WSP qual-name
deprecated-dir   = "DEPRECATED" WSP string-literal
protected-dir    = "PROTECTED"
drop-cascade-dir = "DROP CASCADE"

grants-block and revocations-block are defined in Section 7.10, security-label-decl in Section 14.11, and the Name Map directives in Appendix D.2.3.

4.2.2. Object Section Layout

Sections 5 to 14 describe each object kind in the same order:

1. PG equivalent: the PostgreSQL statement Part 1 corresponds to, linked to its PostgreSQL reference page.
2. Part 1: PostgreSQL’s grammar for that statement, without the leading verb, for the PostgreSQL versions this specification targets (Section 1.4). It is defined by reference and not repeated here. ABNF appears only for what DPG adds to Part 1.
3. Part 2: the common directives the block accepts (Section 4.2.1), then the kind’s own directives, each tagged [DPG] or [PG: ...], with ABNF for the block.
4. Diffing semantics: the statements DPG emits for each change, and their safety class (Section 17.2).
5. Notes: DPG-specific rules. PostgreSQL’s own meaning of a clause is not restated; where a note concerns PostgreSQL behaviour that changes what DPG does, it is tagged [PG].

4.3. The No-Verb Mandate

The keywords CREATE, ALTER, and DROP are PROHIBITED in DPG source files at the declaration level. The compiler MUST reject any source file containing these keywords outside of:

a) Dollar-quoted function, procedure, or aggregate bodies (AS $$...$$, AS $tag$...$tag$), which are opaque text and not interpreted by the compiler (Section 4.6).

b) MIGRATE REMOVE { ... } blocks on enum types (Section 5.1), whose body is DML passthrough.

Rationale: the presence of imperative verbs indicates a migration file rather than a state description. Prohibiting them enforces the declarative contract at parse time, producing an early diagnostic (DPG-E006) rather than silent misuse.

4.4. Structural Scoping

The { } block of any container provides a scope. Nested object declarations inherit their containing context:

• A TABLE declared inside a SCHEMA { } block inherits the schema name; the developer does not repeat it.

• An INDEX declared inside a TABLE’s { } block inherits the table and schema; the developer writes only the index name and column list.

• A POLICY declared inside a TABLE’s POLICIES { } block inherits the table, schema, and database.

Schemas have no ( ) list. Their { } block directly holds all schema-level attributes and nested objects.

[DPG] Explicit schema qualification. Outside any schema scope, a declaration MAY qualify its own name with any schema. Inside a schema scope (a SCHEMA { } block or a schemas/<name>/ directory, Section 3.1), a qualifier on the declared object’s own name MUST name that same schema; any other schema is error DPG-E040. This applies only to the name being declared: references (REFERENCES other.t, function bodies) and RENAMED FROM other_schema.old (Section 7.6) are unaffected. Reason: a declaration’s schema is decided by where it is written, so a qualifier may confirm the scope but never contradict it.

An unqualified declaration outside any schema scope belongs to the database’s default_schema (Section 3.4).

4.5. Statement Terminators

[DPG] One rule governs how every declaration ends:

1. Part 1 ends at its own last token, whatever that is (see the table below).
2. If a { } block follows, it begins immediately after Part 1: a ; between Part 1 and { is error DPG-E037.
3. If no { } block follows, Part 1 MUST end with ; (error DPG-E038).
4. A ; immediately after a block’s closing } is permitted and never required.

The single exception is SCHEMA (Section 6.1): it has no Part 1 terminator, because its body is always its { } block.

terminator = ";" / WSP* part2 [ WSP* ";" ]

For reference, Part 1’s last token by kind (informative):

Reason: one rule with no per-kind exceptions, so a reader and an implementation never need to know a declaration’s kind to know how it ends.

Examples:

-- table with a { } block: no ';' before '{'
TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    CONSTRAINT pk PRIMARY KEY (id)
) {
    INDEXES { idx_email (email); }
}

-- view with a { } block
VIEW active_users AS SELECT id FROM users WHERE active {
    GRANTS { SELECT TO app_readonly; }
}

-- function with a { } block: '{' directly after the closing $$
FUNCTION foo() RETURNS TEXT LANGUAGE sql STABLE
AS $$
    SELECT 'hello';
$$ {
    COMMENT 'Returns hello';
}

-- no { } block: ';' required
TABLE log (
    id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    msg  TEXT NOT NULL
);

4.6. Dollar-Quoted String Parsing

Dollar-quoted strings use the PostgreSQL syntax $$...$$ or $tag$...$tag$ where tag is any identifier string (including the empty string).

The compiler’s dollar-quote parser MUST implement the following algorithm:

1. On encountering the token AS followed by optional whitespace followed by a dollar-quoted delimiter $[tag]$, record the opening delimiter string D.

2. Scan forward byte-by-byte without interpreting any content. No brace counting, no keyword scanning, no SQL parsing occurs inside the dollar-quoted region. Embedded {, }, ;, CREATE, ALTER, DROP, or any other DPG keyword are treated as plain text.

3. The first occurrence of the exact byte sequence D (the same opening delimiter) encountered during the scan terminates the dollar-quoted region. Partial matches do NOT terminate.

4. The bytes immediately following the closing D are ; when no { } block follows, or the block’s opening { when one does (Section 4.5).

5. Named dollar-quoting ($body$, $func$, $sql$, or any $identifier$) is fully supported. The opening and closing tag MUST match exactly, including case.

This algorithm allows function bodies to contain any content including embedded SQL DML, PL/pgSQL blocks, Python, Perl, JavaScript, nested dollar-quoted strings, or arbitrary binary data encoded as text — without any escaping or modification.

4.7. Macro Preprocessor

The macro preprocessor runs as the first phase of compilation, before any parsing. Macros are source-level text fragments that are expanded inline at points of use.

4.7.1. Macro Declaration

A macro declaration uses the MACRO keyword and has one of two body forms.

macro-decl  = "MACRO" WSP identifier WSP paren-body
            / "MACRO" WSP identifier WSP brace-body

paren-body  = "(" *( column-def "," ) column-def ")"
brace-body  = "{" *( block-directive ";" ) "}"

• A paren-body macro contains a comma-separated list of column definitions, exactly as they would appear inside a TABLE ( ) list. The opening ( and closing ) are part of the body and are stripped when the macro is expanded.

• A brace-body macro contains zero or more block directives, exactly as they would appear inside a { } block. The opening { and closing } are part of the body and are stripped when the macro is expanded.

A MACRO declaration generates no SQL whatsoever. It MUST appear at the top level of a .dpg file (not inside any { } or ( ) block). A MACRO declaration inside a block is a compiler error (DPG-E007).

Examples:

MACRO common_timestamps (
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ
)

MACRO audit_block {
    OWNER app_admin;
    ENABLE ROW LEVEL SECURITY;
}

4.7.2. Macro Spread

The spread operator ...name expands a macro inline at the point of use.

spread = "..." identifier

A paren-body macro MUST only be spread inside a ( ) list. Spreading it inside a { } block is a compiler error (DPG-E008).

A brace-body macro MUST only be spread inside a { } block. Spreading it inside a ( ) list is a compiler error (DPG-E009).

Spreading an undefined macro name is a compiler error (DPG-E010).

Example:

TABLE accounts (
    id         UUID NOT NULL DEFAULT gen_random_uuid(),
    ...common_timestamps,
    CONSTRAINT pk_accounts PRIMARY KEY (id)
)
{
    ...audit_block
}

The compiler expands ...common_timestamps to:

    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ

and ...audit_block to:

    OWNER app_admin;
    ENABLE ROW LEVEL SECURITY;

Expansion is performed textually before tokenization. The resulting source text is then tokenized and parsed as if written verbatim.

4.7.3. Macro Scoping Rules

• Macros are scoped to one compilation unit: a database, or a cluster’s objects directory (Section 3.1). A macro defined in any .dpg file of the unit is visible in every other file of the same unit, and in no other unit. The compiler performs a global collection pre-pass over all source files before any file is tokenized, so declaration order across files does not matter.

• When the same macro name is defined in multiple files, the file-local definition takes precedence over any globally-collected definition. This allows individual files to override a shared macro with a specialised version.

• A macro name MUST be unique within its file. Redefining a macro name in the same file is a compiler error (DPG-E011).

• Macros MAY NOT be recursive (a paren-body may not contain a spread of itself or of any other macro that eventually spreads it). Circular macro references are a compiler error (DPG-E012).

• The MACRO keyword is a DPG preprocessor keyword and does not violate the No-Verb Mandate (Section 4.3).

4.8. Dual Definition Modes

For every collection-typed sub-object (indexes, policies, triggers, grants, revocations, partitions, column blocks), DPG supports two equivalent syntactic modes that MAY be freely mixed within the same file:

Mode A — Plural block: The collection keyword is the block header; individual entries omit the singular keyword.

INDEXES {
    idx_email  (email);
    idx_status (status) WHERE (status = 'active');
}

Mode B — Singular keyword: The singular keyword precedes each individual entry outside a plural block.

INDEX idx_email  (email);
INDEX idx_status (status) WHERE (status = 'active');

Both forms are semantically identical. The compiler MUST merge them into a single logical collection before differencing.

[DPG] The singular keyword is always PostgreSQL’s own keyword for the sub-object, and the plural block header is that keyword with an S appended. Any future collection kind follows the same rule. The complete mapping is:

4.9. Block Merging

When the same object is declared across multiple .dpg files (the same schema-qualified name and kind), the compiler MUST merge all declarations into a single logical object. Merge semantics follow Section 3.7.

Merging operates on parsed declarations, not raw source text: each declaration is parsed and built on its own (Sections 15.4-15.7), and declarations of the same name and kind are then merged (Section 15.8).

4.10. Identifiers

DPG follows PostgreSQL’s identifier rules:

• An unquoted identifier consists of letters (including Unicode letters), digits, dollar signs, and underscores. It MUST begin with a letter or underscore. Unquoted identifiers are case-folded to lowercase.

• A quoted identifier is enclosed in double-quotes ("). It may contain any character except a double-quote. To include a literal double-quote, write "". Quoted identifiers are case-sensitive and preserve their original casing.

[DPG] These rules apply identically inside { } blocks: an unquoted name in a block directive (OWNER App_Admin, COLUMN Email, an index column) is folded to lowercase exactly as PostgreSQL folds the same name in Part 1. In a role list, PUBLIC, CURRENT_USER, CURRENT_ROLE, and SESSION_USER are keywords, not role names.

Schema-qualified names use the form schema.name or, for nested objects, schema.table.column. The compiler resolves unqualified names using the enclosing scope context established by SCHEMA { } blocks, falling back to the database.default_schema configuration value.

5. Type System

5.1. ENUM Types

PG equivalent: CREATE TYPE name AS ENUM (...)

Part 1: PostgreSQL’s CREATE TYPE ... AS ENUM without CREATE.

Part 2: OWNER, COMMENT, RENAMED FROM (Section 4.2.1), and:

enum-block     = *( enum-directive )
enum-directive = ( comment-dir / owner-dir / renamed-from-dir
                 / value-renamed-dir ) ";"
               / migrate-remove-dir

value-renamed-dir  = "VALUE" WSP string-literal WSP
                     "RENAMED FROM" WSP string-literal
migrate-remove-dir = "MIGRATE REMOVE" WSP "(" string-literal
                     *( "," WSP string-literal ) ")" WSP
                     "{" <DML statements> "}"

VALUE ... RENAMED FROM names the current label first, in the same order as every other RENAMED FROM. It is a block directive because PostgreSQL’s value list accepts only string literals.

Examples:

TYPE user_status AS ENUM ('active', 'suspended', 'deleted');

TYPE invoice_status AS ENUM ('draft', 'sent', 'paid', 'void', 'overdue') {
    COMMENT 'Billing lifecycle states for customer invoices';
}

-- Remove 'cancelled', moving its rows to 'delivered' first
TYPE order_status AS ENUM ('pending', 'confirmed', 'shipped', 'delivered') {
    MIGRATE REMOVE ('cancelled') {
        UPDATE orders SET status = 'delivered' WHERE status = 'cancelled';
    }
}

-- 'fulfilled' in the snapshot becomes 'delivered'
TYPE order_status AS ENUM ('pending', 'confirmed', 'shipped', 'delivered') {
    VALUE 'delivered' RENAMED FROM 'fulfilled';
}

Diffing semantics:

5.1.1. Adding and Renaming ENUM Values

A value present in the source but absent from the snapshot emits:

ALTER TYPE <schema>.<name> ADD VALUE '<new_value>' [BEFORE | AFTER '<anchor>'];

Safety SAFE. It runs inside the migration’s transaction, except in one case. [PG] PostgreSQL rejects using an enum value inside the transaction that added it. So when any other statement in the same migration uses a value being added (a column DEFAULT, a CHECK, a partial index predicate, a view), every ADD VALUE for that enum is emitted as ADD VALUE IF NOT EXISTS and runs before the transaction begins (Section 17.3). IF NOT EXISTS makes a retry harmless if the transaction later fails.

[DPG] Position. A new value’s position is taken from where it sits in the source list. Walking the list left to right, each new value is emitted AFTER the nearest earlier value that already exists (including one added or renamed earlier in the same migration), or BEFORE the nearest later existing value when there is no earlier one. With neither, it is emitted without an anchor.

[DPG] Renaming. VALUE 'new' RENAMED FROM 'old'; resolves like every other RENAMED FROM (Section 7.6):

• 'old' in the snapshot and 'new' absent: emit ALTER TYPE <schema>.<name> RENAME VALUE 'old' TO 'new' (SAFE).
• 'new' already in the snapshot: no-op.
• Neither in the snapshot: error DPG-E021 (stale RENAMED FROM).

5.1.2. Removing ENUM Values

[PG] PostgreSQL cannot remove a value from an enum. [DPG] MIGRATE REMOVE names the removed values and the DML that moves existing data off them; the type is then rebuilt.

When a value is absent from the source but present in the snapshot, and a MIGRATE REMOVE directive covers it, the compiler MUST emit the following sequence, all within a single transaction:

1. CREATE TYPE <schema>.<name>__dpg_new AS ENUM (<reduced-values>); — a new type with the value removed.

2. Execute each DML statement in the MIGRATE REMOVE body verbatim.

3. Verify that no rows in any column typed as <name> still hold the removed value. If any remain, the compiler MUST abort the transaction and report error DPG-E013 with a table-by-table row count.

4. For each table column typed as <name>:

   ALTER TABLE <schema>.<table>
       ALTER COLUMN <col> TYPE <schema>.<name>__dpg_new
       USING <col>::text::<schema>.<name>__dpg_new;
   

5. DROP TYPE <schema>.<name>;

6. ALTER TYPE <schema>.<name>__dpg_new RENAME TO <name>;

On any failure in steps 2–6: DROP TYPE IF EXISTS <schema>.<name>__dpg_new; and rollback.

When a value is absent from the source but no MIGRATE REMOVE directive covers it, the compiler MUST emit a diagnostic (DPG-E014, unguarded ENUM value removal) and refuse to proceed without the --allow-destructive flag. Even with --allow-destructive, the compiler MUST attempt to verify that no rows hold the removed value before proceeding.

MIGRATE REMOVE is the only place DPG runs user DML (Section 1.1).

5.1.3. Reordering ENUM Values

[PG] An enum’s value order is fixed. [DPG] Reordering existing values is treated as a remove and an add and classified as DESTRUCTIVE, unless every moved value is new (not in the snapshot).

5.2. Composite Types

PG equivalent: CREATE TYPE name AS (...)

Part 1: PostgreSQL’s CREATE TYPE ... AS (...) without CREATE.

Part 2: OWNER, COMMENT, RENAMED FROM (Section 4.2.1), and:

composite-block     = *( composite-directive )
composite-directive = ( comment-dir / owner-dir / renamed-from-dir ) ";"
                    / attribute-block
                    / attributes-block

attribute-block  = "ATTRIBUTE" WSP identifier WSP "{" *attr-directive "}"
attributes-block = "ATTRIBUTES" WSP "{"
                     *( identifier WSP "{" *attr-directive "}" ) "}"
attr-directive   = ( comment-dir / "RENAMED FROM" WSP identifier ) ";"

[DPG] ATTRIBUTE/ATTRIBUTES use PostgreSQL’s own keyword for a composite type member. An ATTRIBUTE block naming an attribute absent from the ( ) list is error DPG-E018. COLUMN in a composite type block, and ATTRIBUTE in any other block, is error DPG-E024.

Example:

SCHEMA public {
    TYPE address AS (
        street      TEXT COLLATE "en_US",
        city        TEXT,
        state       CHAR(2),
        postal_code TEXT,
        country     CHAR(2)
    )
    {
        OWNER schema_admin;
        ATTRIBUTE postal_code { COMMENT 'ZIP or postcode'; }
    }
}

Diffing semantics:

5.3. Range Types

PG equivalent: CREATE TYPE name AS RANGE (...)

Part 1: PostgreSQL’s CREATE TYPE ... AS RANGE (...) without CREATE.

Part 2: OWNER, COMMENT, RENAMED FROM (Section 4.2.1).

range-block = *( ( comment-dir / owner-dir / renamed-from-dir ) ";" )

Example:

SCHEMA public {
    TYPE float8range AS RANGE (
        SUBTYPE      = float8,
        SUBTYPE_DIFF = float8mi
    );
}

Multirange types: [PG] PostgreSQL creates a multirange type with every range type and drops it with the range, so DPG never declares it separately. Its name is PostgreSQL’s default (the range’s name with its first range replaced by multirange, floatrange giving floatmultirange, or with _multirange appended when the name contains no range) unless Part 1 sets MULTIRANGE_TYPE_NAME = name.

Diffing semantics:

5.4. Domain Types

PG equivalent: CREATE DOMAIN

Part 1: PostgreSQL’s CREATE DOMAIN without CREATE, so DEFAULT and constraints are written there, as PostgreSQL writes them. DPG adds two things to a domain constraint:

domain-constraint = <PostgreSQL's domain_constraint>
                    [ WSP "NOT VALID" ]
                    [ WSP "RENAMED FROM" WSP identifier ]

• [DPG] NOT VALID on a domain constraint. [PG] CREATE DOMAIN has no NOT VALID; it exists only on ALTER DOMAIN ... ADD CONSTRAINT. It therefore takes effect only when the constraint is added to a domain that already exists, and is left out of the CREATE DOMAIN for a new domain. Removing it later validates the constraint.
• [DPG] RENAMED FROM old on a named constraint renames it instead of dropping and re-adding it.

Part 2: OWNER, COMMENT, RENAMED FROM, GRANTS, REVOKES (Section 4.2.1).

domain-block = *( ( comment-dir / owner-dir / renamed-from-dir
                   / grants-block / revocations-block ) ";" )

Example:

SCHEMA public {
    DOMAIN positive_integer AS INTEGER
        DEFAULT 1
        CONSTRAINT positive_only  CHECK (VALUE > 0)
        CONSTRAINT reasonable_max CHECK (VALUE < 1000000);
}

Diffing semantics:

5.5. Base (Shell) Types

PG equivalent: CREATE TYPE name (INPUT = ..., OUTPUT = ..., ...), or the shell form CREATE TYPE name

Part 1: PostgreSQL’s base-type and shell-type CREATE TYPE forms without CREATE.

Part 2: OWNER, COMMENT, RENAMED FROM (Section 4.2.1).

base-type-block = *( ( comment-dir / owner-dir / renamed-from-dir ) ";" )

SCHEMA public {
    TYPE mytype (
        INPUT          = mytype_in,
        OUTPUT         = mytype_out,
        INTERNALLENGTH = 16,
        ALIGNMENT      = double
    );
}

Shell types: TYPE name; declares a shell type on its own.

[DPG] Support functions that reference the type. When a new base type’s support functions are also new and take or return the type itself (FUNCTION mytype_in(cstring) RETURNS mytype), the dependency graph (Section 22) drops the function-to-type ordering edge for them, so the CREATE FUNCTION statements are emitted before the CREATE TYPE. [PG] PostgreSQL creates the shell type itself when such a function references a type that doesn’t exist yet, and the full CREATE TYPE then completes it. The source declares only the full type.

Diffing semantics: [PG] ALTER TYPE name SET (...) can change only RECEIVE, SEND, TYPMOD_IN, TYPMOD_OUT, ANALYZE, SUBSCRIPT and STORAGE; every other property is fixed at creation. Properties are compared one by one:

5.6. Virtual Types

Virtual types are DPG-native DDL constructs that give a structural schema to JSON / JSONB columns and JSON array columns. They have no backing PostgreSQL type — no CREATE TYPE, ALTER TYPE, or DROP TYPE is ever emitted. A table column or composite type attribute may declare its type as a virtual type name; DPG resolves that reference to jsonb (scalar) or jsonb[] (array) in all generated SQL. The structured body is stored in the snapshot so downstream consumers (ORM generators, type-safe query builders) can read type information from the snapshot JSON.

PG equivalent: none. The whole declaration is DPG grammar ([DPG]).

Part 2: COMMENT (Section 4.2.1, kept in the snapshot only) and PREFERRED JSON FORMAT json | jsonb [DPG].

virtual-type-decl = "VIRTUAL TYPE" WSP schema-name WSP "AS" WSP
                    vtype-body ";" [ "{" vtype-block "}" ]

vtype-body      = vtype-union
vtype-union     = vtype-term *( WSP "|" WSP vtype-term )
vtype-term      = vtype-composite / vtype-typeref
vtype-composite = "(" vtype-field *( "," WSP vtype-field ) ")"
vtype-field     = identifier WSP vtype-typeref
vtype-typeref   = [ schema-name "." ] identifier [ "[]" ]

vtype-block = *( comment-dir / preferred-json-format-dir ) ";"

preferred-json-format-dir = "PREFERRED" WSP "JSON" WSP "FORMAT" WSP
                            ( "json" / "jsonb" )

The compiler MUST parse and validate the body. A vtype-typeref that appears in a column or composite attribute position resolves to the virtual type’s preferred JSON format (json or jsonb, default jsonb) in SQL output; the virtual type name is never written to the database.

Body forms:

• Type reference — a single PG built-in or user-defined type name, or a reference to another declared VIRTUAL TYPE:

  VIRTUAL TYPE label AS text;
  VIRTUAL TYPE metric AS numeric;
  VIRTUAL TYPE location AS point;           -- PostgreSQL's point type
  VIRTUAL TYPE tags AS label[];             -- a JSON array of label values
  

• Composite — an inline record with named, typed fields. Field types MAY themselves be virtual type references:

  VIRTUAL TYPE coordinate AS (x float8, y float8);
  
  VIRTUAL TYPE line_item AS (
      sku      text,
      quantity integer,
      price    numeric,
      tags     label[]
  );
  

• Union — two or more terms joined with |. Any combination of composites and type references is valid:

  VIRTUAL TYPE payment AS
      (kind text, amount numeric, currency text)
      | (kind text, token text)
      | text;
  

Usage as a column or field type:

TABLE orders (
    id    bigint,
    items line_item[]   -- virtual type ref: emits "jsonb[]" in SQL
) { }

TYPE address AS (
    street text,
    detail address_detail   -- virtual type ref: emits "jsonb"
);

Arrays. [] means an array of the type it follows, in the position where it is written:

• Inside a virtual type body, T[] is a JSON array whose elements are T values (tags label[] above).
• On a column or composite attribute, T[] is a PostgreSQL array, as it is for every other type: items line_item[] is a jsonb[] column, and each element is one line_item value. A column that holds a single JSON array uses a virtual type whose body is the array (VIRTUAL TYPE line_items AS line_item[];, then items line_items, a jsonb column).

Name resolution. A virtual type shares PostgreSQL’s type namespace, so a type name in DPG source never means two things:

• A virtual type’s name MUST NOT be a PostgreSQL built-in type name (any type in pg_catalog, or a SQL spelling of one, such as integer or boolean). Its qualified name MUST NOT already be used by a declared type (including a range’s multirange type) or by a table, view, materialized view, or foreign table, each of which has a row type of the same name. Either is error DPG-E044. The same name in a different schema is not a conflict.
• An unqualified type reference on a column, a composite attribute, or inside a virtual type body names a virtual type when one of that name exists in the referencing object’s own schema, or else in the database’s default_schema (Section 3.4). Otherwise it is an ordinary PostgreSQL type name. A schema-qualified reference names exactly that schema’s virtual type, if one exists.

Logical type in the snapshot. The snapshot records the full declared type of every column that uses a virtual type, for tools such as client generators. A column record holds type (the json/jsonb type PostgreSQL stores) and virtual_type (the schema-qualified reference as declared, for example "public.line_item[]"). Inside a virtual type record, every type reference that names a virtual type carries "virtual": true and its schema; any other reference names a PostgreSQL type (Section 16.3).

Rules:

• VIRTUAL TYPE MAY be schema-qualified; an unqualified one belongs to its schema scope, or else to default_schema (Section 4.4).

• No CREATE TYPE, ALTER TYPE, or DROP TYPE is EVER emitted for a virtual type.

• Virtual types appear in the snapshot under "kind": "virtual_type" for round-trip consistency. dpg plan produces no SQL for additions, modifications, or removals of virtual types.

• When a column or composite attribute type resolves to a virtual type, the generated SQL uses jsonb / jsonb[]. The virtual type name is never written to the database catalog.

• The { } block accepts COMMENT and PREFERRED JSON FORMAT. Any other directive is a compiler error (DPG-E015).

• PREFERRED JSON FORMAT json causes DPG to emit json / json[] instead of the default jsonb / jsonb[] when this virtual type is used as a column or attribute type.

6. Schema and Namespace Objects

6.1. SCHEMA

PG equivalent: CREATE SCHEMA

Part 1: none. [DPG] A schema is declared by its name and a { } block, which also holds the objects declared in it (Section 4.4); AUTHORIZATION role is written as OWNER role. A file under schemas/<name>/ declares the schema’s own settings with the same form (Section 3.1).

Part 2: OWNER, COMMENT, RENAMED FROM, GRANTS (Section 4.2.1), DEFAULT PRIVILEGES (Section 11.4), and nested object declarations.

schema-decl = "SCHEMA" WSP identifier
              "{" schema-block "}"

schema-block = *( schema-directive / nested-object )

schema-directive = owner-dir
                 / comment-dir
                 / renamed-from-dir
                 / grants-block
                 / default-privileges-decl

Examples:

SCHEMA analytics {
    OWNER analytics_role;
    COMMENT 'Derived tables and event aggregations';

    TABLE events ( ... ) { ... }
    FUNCTION compute_daily() RETURNS VOID LANGUAGE plpgsql AS $$ ... $$;
}

SCHEMA reporting {
    RENAMED FROM old_reporting;
}

[PG] From PostgreSQL 15, a new cluster’s public schema no longer grants CREATE to PUBLIC. A project that relies on that grant declares it (GRANTS { CREATE TO PUBLIC; } on public).

Diffing semantics:

6.2. EXTENSION

Extensions are database-level objects and MUST be declared in a database .dpg file, not in the cluster objects directory.

PG equivalent: CREATE EXTENSION

Part 1: PostgreSQL’s CREATE EXTENSION without CREATE (and without IF NOT EXISTS, which DPG adds itself).

Part 2: COMMENT (Section 4.2.1).

Examples:

EXTENSION pgcrypto;
EXTENSION postgis SCHEMA public VERSION '3.3';
EXTENSION pg_trgm CASCADE;

Diffing semantics:

[PG] SET SCHEMA works only for a relocatable extension; PostgreSQL rejects it for the others.

7. Tables

7.1. Table Declaration Syntax

PG equivalent: CREATE [UNLOGGED] TABLE, CREATE FOREIGN TABLE

Part 1: PostgreSQL’s CREATE TABLE (including the OF type_name form and PARTITION BY) or CREATE FOREIGN TABLE, without CREATE. DPG adds a RENAMED FROM suffix to a table constraint (Section 7.3). PARTITION OF is not written in Part 1; partitions are declared in the parent’s PARTITIONS block (Section 7.13).

Part 2: OWNER, COMMENT, RENAMED FROM, DEPRECATED, PROTECTED, DROP CASCADE, GRANTS, REVOKES, SECURITY LABEL, NAME MAP (Section 4.2.1), and:

table-block  = *( table-directive )

table-directive = owner-dir / comment-dir / renamed-from-dir
                / detached-from-dir / protected-dir / deprecated-dir
                / drop-cascade-dir / rls-enable-dir / rls-force-dir
                / replica-identity-dir / cluster-dir
                / column-block / indices-block / policies-block
                / triggers-block / grants-block / revocations-block
                / constraint-decl / partitions-block

Notes:

• [DPG] LIKE source_table is resolved by the compiler: the source must be a table declared in the same compile unit, and the table is diffed and snapshotted with the resolved columns and constraints, not the LIKE clause. LIKE options apply in order, as in PostgreSQL.
• [DPG] A table declared OF type_name is compared column by column like any other. Gaining, losing or changing OF type_name later emits ALTER TABLE t OF type_name / NOT OF (Section 7.11).
• [DPG] Changing USING method, INHERITS (...), WITH (...) or TABLESPACE after creation emits the targeted ALTER TABLE statements in Section 7.11; the table is not recreated.

TABLE order_items_archive (
    LIKE order_items INCLUDING DEFAULTS INCLUDING CONSTRAINTS,
    archived_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

TYPE address AS (street TEXT, city TEXT, zip TEXT);

TABLE shipping_address OF address (
    street WITH OPTIONS NOT NULL,
    CONSTRAINT zip_format CHECK (zip ~ '^[0-9]{5}$')
);

7.2. Column Definitions

Part 1: columns are written in the ( ) list with PostgreSQL’s CREATE TABLE column syntax. [DPG] A column’s type MAY also be a virtual type (Section 5.6) or SERIAL/BIGSERIAL/SMALLSERIAL (Appendix D.3).

Notes:

• [DPG] PRIMARY KEY implies NOT NULL. The compiler treats every PRIMARY KEY column, inline or table-level, as NOT NULL, as PostgreSQL does. A redundant NOT NULL is accepted; the compiler emits no redundant NOT NULL in CREATE TABLE and no SET NOT NULL/DROP NOT NULL for a primary-key column.
• [DPG] Inline and table-level constraints are equivalent. The compiler emits single-column PRIMARY KEY, UNIQUE, CHECK and REFERENCES constraints inline in CREATE TABLE, named ones included; multi-column constraints stay table-level.
• [DPG] Generated and identity columns are diffed per Section 7.4’s tables.

-- Inline form
TABLE accounts (
    id   UUID    NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
    slug TEXT    NOT NULL UNIQUE,
    org  UUID    NOT NULL REFERENCES organisations (id) ON DELETE CASCADE
);

-- Table-level (needed for multi-column constraints)
TABLE order_items (
    order_id   BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    CONSTRAINT pk_order_items PRIMARY KEY (order_id, product_id)
);

Column type changes. ALTER TABLE t ALTER COLUMN c TYPE new [USING expr] rewrites the table under an ACCESS EXCLUSIVE lock, so it is at least CAUTION. [DPG] It is CAUTION when a USING expression is given (COLUMN c { USING expr; }, Section 7.4) or when PostgreSQL converts the stored values without loss, and DESTRUCTIVE otherwise. PostgreSQL converts without loss:

• between two different types that have an implicit cast (pg_cast.castcontext = 'i', for example smallint to integer);
• within one type, when the modifier widens:

  Type	CAUTION when
  character varying(n), bit varying(n)	the length grows, or is removed
  character(n)	the length grows (bare character means character(1))
  bit(n)	never: PostgreSQL rejects any length change of fixed bit
  numeric(p,s)	neither precision nor scale shrinks, or both are removed
  time(p), timestamp(p) (with or without time zone), interval(p)	the precision grows, or is removed
  arrays of any of these	never

Reason: a shrinking numeric scale or time precision does not fail; PostgreSQL silently rounds or truncates the stored values, so it is treated as data loss.

7.3. Constraints

Part 1: PostgreSQL’s table and column constraint syntax, in the ( ) list.

Part 2: CONSTRAINT name ... entries in the { } block, which describe constraints added to an existing table [PG: ALTER TABLE ... ADD CONSTRAINT]. [DPG] A constraint in either place MAY carry a RENAMED FROM suffix.

constraint-decl = "CONSTRAINT" WSP identifier WSP
                  <PostgreSQL's table constraint, after its name>
                  [ WSP "RENAMED FROM" WSP identifier ] ";"
                / "CONSTRAINTS" WSP "{" *( identifier WSP
                  <PostgreSQL's table constraint, after its name>
                  [ WSP "RENAMED FROM" WSP identifier ] ";" ) "}"

[DPG] Constraints are identified by name and merged across the ( ) list and the { } block: the same name and definition in both is one constraint; the same name with different definitions is DPG-E005.

NOT VALID lifecycle [DPG]:

1. A constraint declared NOT VALID on an existing table is added with ALTER TABLE t ADD CONSTRAINT c ... NOT VALID (CAUTION).
2. Removing NOT VALID in source emits ALTER TABLE t VALIDATE CONSTRAINT c (CAUTION).
3. The validated constraint MAY then be moved into the ( ) list; it is matched by name and nothing is emitted.

NOT VALID MAY be written in the ( ) list or on a { } block entry. A new table is created with its constraints in the ( ) list; the { } block form describes a constraint added to an existing table.

Diffing semantics [DPG]:

Foreign key cycles [DPG]: when tables reference each other and the foreign keys in the cycle are DEFERRABLE, the tables are created first and those foreign keys added afterwards with ALTER TABLE ... ADD CONSTRAINT ... DEFERRABLE. A cycle with no DEFERRABLE foreign key is error DPG-E017, listing the cycle.

Temporal keys [DPG]: a PRIMARY KEY/UNIQUE with WITHOUT OVERLAPS, or a FOREIGN KEY with PERIOD, is diffed as the PRIMARY KEY/UNIQUE/FOREIGN KEY PostgreSQL catalogues it as. [PG] Non-range key columns need the btree_gist extension.

TABLE room_bookings (
    room_id  BIGINT NOT NULL,
    valid_at DATERANGE NOT NULL,
    CONSTRAINT no_double_booking
        PRIMARY KEY (room_id, valid_at WITHOUT OVERLAPS)
);

Constraints kept on partitions [DPG]: [PG] ALTER TABLE ONLY parent DROP CONSTRAINT name (PostgreSQL 18) drops a constraint from a partitioned parent while keeping it, as a local constraint, on every existing partition. To declare that, a partition’s PARTITIONS { } entry MAY carry its own { } body with CONSTRAINT / CONSTRAINTS entries:

partition-decl       =/ ... [ WSP "{" partition-block-body "}" ]
partition-block-body = *( ( "PARTITIONS" WSP "{" *partition-decl "}" )
                         / constraint-decl )

When a constraint disappears from the parent while a direct partition declares a constraint of the same name, the compiler emits ALTER TABLE ONLY parent DROP CONSTRAINT name (SAFE) instead of the ordinary cascading drop. Otherwise a partition’s own constraints are added and dropped with ordinary ALTER TABLE partition ADD/DROP CONSTRAINT.

• This is the only structure a partition declares independently of its parent. It does not apply to an ATTACHED FROM entry, whose constraints come from its own TABLE declaration.
• ONLY keeps the constraint on every existing partition. Keeping it on only some takes two migrations: the ONLY drop, then ordinary drops on the others.
• Only direct partitions are matched against the parent.

Example:

TABLE orders (
    id         BIGINT GENERATED ALWAYS AS IDENTITY,
    account_id BIGINT NOT NULL,
    amount     NUMERIC(10,2) NOT NULL,
    CONSTRAINT pk_orders PRIMARY KEY (id)
)
{
    -- added to an existing table, validated in a later migration
    CONSTRAINT ck_amount_positive CHECK (amount > 0) NOT VALID;

    CONSTRAINT fk_account FOREIGN KEY (account_id)
        REFERENCES accounts (id)
        ON DELETE CASCADE
        DEFERRABLE INITIALLY DEFERRED;
}

7.4. The COLUMN Reference Block

[DPG] Inside a table’s { } block, COLUMN name { ... } refers to a column declared in the ( ) list and holds what PostgreSQL sets on a column with separate statements.

column-block    = "COLUMN" WSP col-name WSP "{" col-block-body "}"
               /  "COLUMNS" WSP "{" *col-named-block "}"

col-named-block = col-name WSP "{" col-block-body "}"

col-block-body  = *( col-block-directive ";" )

col-block-directive
    = comment-dir
    / "STATISTICS" WSP ( integer / "DEFAULT" )
    / "COMPRESSION" WSP identifier
    / "STORAGE" WSP identifier
    / deprecated-dir
    / "USING" WSP expr
    / "RENAMED FROM" WSP col-name
    / grants-block
    / revocations-block
    / security-label-decl

Rules [DPG]:

• COLUMN name MUST name a column in the ( ) list (DPG-E018). A COLUMN block never declares a column.
• After a rename, the block uses the new name; RENAMED FROM carries the old one. Any index or constraint in the { } block that still names the old column is error DPG-E019.
• A statistics target above 10000 is error DPG-E020.

Generated columns [DPG]: a change to a column’s GENERATED ALWAYS AS (expr) STORED | VIRTUAL clause is diffed as follows, rather than as a type change. [PG] PostgreSQL cannot turn a plain column into a generated one, switch STORED/VIRTUAL, or drop the expression of a VIRTUAL column in place.

Identity columns [DPG]: a change to GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY [(options)] is diffed as follows:

7.5. Column-Level Grants

GRANTS { } and REVOKES { } inside a COLUMN name { } block [PG: GRANT/REVOKE privilege (col) ON TABLE ...] apply to that column. They follow the same additive model as table-level grants (Section 11.2): DPG emits GRANT and never revokes on its own.

GRANT privilege (col) ON TABLE schema.table TO role;

TABLE users ( id BIGINT ..., email TEXT, ssn TEXT )
{
    COLUMN email {
        GRANTS {
            SELECT TO reporting_role;
            SELECT TO analytics_role;
        }
    }

    COLUMN ssn {
        STORAGE extended;
        GRANTS  { SELECT TO compliance_role; }
        REVOKES { ALL PRIVILEGES FROM PUBLIC; }
    }

    GRANTS { SELECT, INSERT, UPDATE TO app_service; }
}

7.6. Column Renaming

[DPG] A column rename is declared with COLUMN new_name { RENAMED FROM old_name; }. The new name appears in the ( ) list and the COLUMN block; the old name appears only after RENAMED FROM.

TABLE users (
    email_address TEXT NOT NULL,
    CONSTRAINT uq_users_email UNIQUE (email_address)
)
{
    COLUMN email_address {
        RENAMED FROM email;
        COMMENT 'Verified email address';
    }
}

Resolution (the same for every RENAMED FROM):

1. Old name in the snapshot, new name not: emit ALTER TABLE users RENAME COLUMN email TO email_address (CAUTION).
2. New name already in the snapshot: the rename has been applied; RENAMED FROM is a no-op.
3. Neither in the snapshot: error DPG-E021 (stale RENAMED FROM).

After the rename, every index and constraint in the block MUST use the new name (DPG-E019).

Objects resolve the same way, by qualified name: TABLE user_accounts { RENAMED FROM users; } emits ALTER TABLE users RENAME TO user_accounts.

Moves between schemas. For a schema-scoped object, the old name MAY be schema-qualified (renamed-from-dir takes a qual-name). An unqualified old name is in the object’s own schema. When the old schema differs, the compiler emits ALTER <kind> old_schema.name SET SCHEMA new_schema and then, if the name also changed, RENAME TO. [PG] PostgreSQL has no single statement that does both.

• TABLE archive.orders { RENAMED FROM public.orders; } emits ALTER TABLE public.orders SET SCHEMA archive.
• VIEW archive.order_summary { RENAMED FROM public.orders_summary; } emits ALTER VIEW public.orders_summary SET SCHEMA archive and ALTER VIEW archive.orders_summary RENAME TO order_summary.

7.7. Indexes

PG equivalent: CREATE INDEX

Part 2 entry [PG: CREATE INDEX]: an index is declared in the table’s INDEXES { } block, or with the singular INDEX form (Section 4.8). An entry is PostgreSQL’s CREATE INDEX without CREATE, INDEX and ON table, which the block supplies, plus a DPG RENAMED FROM suffix:

index-decl = [ "UNIQUE" WSP ] [ "CONCURRENTLY" WSP ] [ "ONLY" WSP ]
             index-name WSP
             <the rest of PostgreSQL's CREATE INDEX, from USING or
              the element list onward>
             [ WSP "RENAMED FROM" WSP index-name ] ";"

UNIQUE, CONCURRENTLY and ONLY keep PostgreSQL’s order. The INDEX form writes [UNIQUE] INDEX [CONCURRENTLY] [ONLY] name ..., with INDEX where PostgreSQL puts it.

Examples:

TABLE users ( email TEXT, status user_status, ... )
{
    INDEXES {
        idx_email             (email);
        UNIQUE idx_unique_slug (slug);
        idx_tenant_created    (tenant_id ASC, created_at DESC);
        idx_active_users      (email) WHERE (status = 'active');
        idx_location          USING gist (location);
        idx_lower_email       (lower(email));
        idx_covering          (user_id) INCLUDE (email, created_at);
        idx_brin              USING brin (created_at)
                                  WITH (pages_per_range = 128);
        idx_search_doc        USING gist (doc tsvector_ops(siglen = 32));
        UNIQUE idx_optional_email (email) NULLS NOT DISTINCT;
        idx_email_v2          (email) RENAMED FROM idx_email_old;
        CONCURRENTLY idx_payload (payload);
    }
}

UNIQUE INDEX idx_unique_slug (slug);
INDEX CONCURRENTLY idx_payload (payload);

Notes [DPG]:

• An index is identified by its name within its schema; two different definitions under one name is error DPG-E005.
• CONCURRENTLY is written per index; there is no project-wide default. On an index added to an existing table it emits CREATE INDEX CONCURRENTLY, a non-transactional CAUTION step run after COMMIT. On a new table’s or materialized view’s index it has no effect: [PG] that index is created in the same transaction as the relation, where PostgreSQL refuses CONCURRENTLY; the linter warns (concurrently-new-table, Section 19.1).
• WHERE predicates and index expressions are compared as text, with runs of whitespace collapsed.
• RENAMED FROM renames the index only when nothing else about it changed; otherwise the index is dropped and recreated.

Diffing semantics:

7.8. Row Level Security

PG equivalent: ALTER TABLE ... ENABLE/FORCE ROW LEVEL SECURITY, CREATE POLICY

Part 2: ENABLE ROW LEVEL SECURITY and FORCE ROW LEVEL SECURITY [PG: ALTER TABLE ... ENABLE/FORCE ROW LEVEL SECURITY]; POLICIES { } entries or the singular POLICY form [PG: CREATE POLICY]. A policy entry is PostgreSQL’s CREATE POLICY without CREATE POLICY and ON table, plus a DPG RENAMED FROM suffix:

rls-enable-dir = "ENABLE ROW LEVEL SECURITY"
rls-force-dir  = "FORCE ROW LEVEL SECURITY"

policy-decl    = policy-name
                 <the rest of PostgreSQL's CREATE POLICY after ON table:
                  [AS ...] [FOR ...] [TO ...] [USING (...)]
                  [WITH CHECK (...)]>
                 [ WSP "RENAMED FROM" WSP policy-name ] ";"

Example:

TABLE orders ( ... )
{
    ENABLE ROW LEVEL SECURITY;
    FORCE ROW LEVEL SECURITY;

    POLICIES {
        view_own FOR SELECT
            USING (user_id = auth.uid());

        update_own FOR UPDATE
            USING      (user_id = auth.uid())
            WITH CHECK (user_id = auth.uid() AND status != 'locked');

        restrict_deleted AS RESTRICTIVE FOR ALL
            USING (deleted_at IS NULL);

        admin_all FOR ALL
            TO admin_role
            USING (true);
    }
}

Diffing semantics:

Notes [DPG]:

• ALTER POLICY is used whenever the change allows it, so a policy is never briefly absent. [PG] FOR and AS cannot be altered, and a rename cannot be combined with other changes in one ALTER POLICY.
• A policy is identified by its table and name; RENAMED FROM takes a bare name, since a policy cannot move to another table.
• RLS settings and policies on a partitioned table are not copied to its partitions; DPG doesn’t propagate them.

7.9. Triggers

PG equivalent: CREATE [CONSTRAINT] TRIGGER

Part 2 entry [PG: CREATE TRIGGER]: a trigger is declared in the table’s TRIGGERS { } block, or with the singular TRIGGER form (Section 4.8). An entry is PostgreSQL’s CREATE [CONSTRAINT] TRIGGER without CREATE, TRIGGER and ON table, with CONSTRAINT written after the name, plus DPG suffixes:

trigger-decl = trigger-name [ WSP "CONSTRAINT" ] WSP
               <the rest of PostgreSQL's CREATE TRIGGER after the name,
                without ON table>
               [ WSP trigger-enable-state ]
               [ WSP depends-on-extension-dir ]
               [ WSP "RENAMED FROM" WSP trigger-name ]
               ";"

trigger-enable-state = "DISABLED" / "ENABLE REPLICA" / "ENABLE ALWAYS"

• trigger-enable-state [PG: ALTER TABLE ... DISABLE/ENABLE REPLICA/ENABLE ALWAYS TRIGGER]. Omitted means enabled, PostgreSQL’s default.
• DEPENDS ON EXTENSION ext [PG: ALTER TRIGGER ... DEPENDS ON EXTENSION].
• RENAMED FROM old [DPG], a bare name: a trigger cannot move to another table.

Example:

TABLE users ( ... )
{
    TRIGGERS {
        before_insert BEFORE INSERT
            FOR EACH ROW
            EXECUTE FUNCTION set_defaults();

        after_email_change AFTER UPDATE OF email
            FOR EACH ROW
            WHEN (OLD.email IS DISTINCT FROM NEW.email)
            EXECUTE FUNCTION notify_email_change();

        audit_changes AFTER INSERT OR UPDATE OR DELETE
            REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows
            FOR EACH STATEMENT
            EXECUTE FUNCTION audit_table_changes();

        check_ref CONSTRAINT AFTER INSERT OR UPDATE
            FROM orders
            DEFERRABLE INITIALLY DEFERRED
            FOR EACH ROW
            EXECUTE FUNCTION check_ref_integrity();
    }
}

Diffing semantics:

Notes [DPG]:

• A trigger is identified by its table and name.
• When a rename comes with another change that recreates the trigger, it is created under the new name directly. [PG] ALTER TRIGGER cannot rename and change DEPENDS ON EXTENSION in one statement, so those are emitted as two statements, the rename first.
• Trigger clauses are passed to PostgreSQL unchanged; DPG does not check clause combinations. PostgreSQL reports an invalid one when the migration runs.
• CREATE RULE is out of scope (Section 23), so rule enable states are not modelled.

7.10. Table-Level Grants and Revocations

Part 2: GRANTS { } [PG: GRANT ... ON TABLE] and REVOKES { } [PG: REVOKE ... ON TABLE]. Every other object kind that accepts grants uses the same blocks.

grants-block      = "GRANTS" WSP "{" *( grant-entry ";" ) "}"
revocations-block = "REVOKES" WSP "{" *( revoke-entry ";" ) "}"

grant-entry  = privilege-list WSP "TO" WSP role-list
               [ WSP "WITH GRANT OPTION" ]
               [ WSP "GRANTED BY" WSP role-spec ]
revoke-entry = ( privilege-list / "ALL PRIVILEGES" ) WSP
               "FROM" WSP role-list
               [ WSP "GRANTED BY" WSP role-spec ]
               [ WSP "CASCADE" ]

privilege-list = privilege *( "," privilege )
privilege      = "SELECT" / "INSERT" / "UPDATE" / "DELETE" /
                 "TRUNCATE" / "REFERENCES" / "TRIGGER" /
                 "USAGE" / "EXECUTE" / "CREATE" / "CONNECT" /
                 "TEMPORARY" / "MAINTAIN" / "ALL" / "ALL PRIVILEGES"

Grants follow the additive model (Section 11.2). [PG] Which privileges apply to which object kind is PostgreSQL’s rule; it rejects a privilege that doesn’t apply. MAINTAIN needs PostgreSQL 17 (Section 3.8).

7.11. Table Lifecycle Directives

The common directives (Section 4.2.1) behave on a table as follows:

• PROTECTED [DPG]: the compiler refuses to emit DROP TABLE for the table, even when it is absent from the desired state (DPG-E022). Removing it requires removing PROTECTED first.
• DEPRECATED 'msg' [DPG]: the table comment gets a [DEPRECATED] prefix, and the linter warns on references to the table (warn_on_deprecated).
• DROP CASCADE [DPG]: the table is dropped with CASCADE, overriding default_drop_behavior.
• RENAMED FROM [DPG]: Section 7.6.

REPLICA IDENTITY and CLUSTER ON [PG: ALTER TABLE ... REPLICA IDENTITY / CLUSTER ON]:

replica-identity-dir = "REPLICA IDENTITY" WSP
                        ( "DEFAULT" / "FULL" / "NOTHING"
                        / "USING INDEX" WSP index-name )
cluster-dir           = "CLUSTER ON" WSP index-name

TABLE orders ( ... )
{
    REPLICA IDENTITY FULL;
    CLUSTER ON idx_orders_created_at;
}

Omitting REPLICA IDENTITY means DEFAULT. CLUSTER ON records the index a manual CLUSTER would use; the CLUSTER command itself is out of scope (Section 23).

Changes after creation [DPG]: these Part 1 clauses are changed in place rather than by recreating the table:

7.12. Unlogged and Foreign Tables

Unlogged tables are declared with UNLOGGED TABLE (Part 1) and emit CREATE UNLOGGED TABLE. Toggling it emits ALTER TABLE ... SET LOGGED/SET UNLOGGED (CAUTION, Section 7.11).

UNLOGGED TABLE session_cache (
    key   TEXT NOT NULL PRIMARY KEY,
    value JSONB
);

Temporary tables [DPG] are session-scoped, and DPG does not manage them. TEMPORARY TABLE in a .dpg file is error DPG-E023.

Foreign tables: SERVER and OPTIONS are Part 1 clauses.

FOREIGN TABLE remote_events (
    id         BIGINT,
    payload    JSONB,
    created_at TIMESTAMPTZ
) SERVER log_server OPTIONS (table_name 'events', schema_name 'public')
{
    COLUMN id { COMMENT 'Remote event primary key'; }
    GRANTS { SELECT TO app_readonly; }
}

Diffing semantics: a foreign table is diffed as a table (Section 21), with two additions:

[PG] ALTER FOREIGN TABLE cannot change the server.

7.13. Partitioned Tables

PG equivalent: CREATE TABLE ... PARTITION BY, CREATE TABLE ... PARTITION OF, ALTER TABLE ... ATTACH/DETACH PARTITION

Part 1: PARTITION BY is written in the parent’s Part 1, as in PostgreSQL.

Part 2: PARTITIONS { } [PG: CREATE TABLE ... PARTITION OF] declares the partitions. An entry is the partition’s name followed by PostgreSQL’s partition bound (FOR VALUES ... or DEFAULT); a partition never restates its parent’s columns.

partitions-block = "PARTITIONS" WSP "{" *partition-decl "}"

partition-decl   = [ "FOREIGN" WSP ] partition-name WSP
                   <PostgreSQL's partition_bound_spec>
                   [ WSP "SERVER" WSP identifier
                     [ WSP "OPTIONS" WSP "(" option-list ")" ] ]
                   [ WSP "RENAMED FROM" WSP partition-name ]
                   [ WSP <PostgreSQL's PARTITION BY clause> WSP
                     "{" "PARTITIONS" WSP "{" *partition-decl "}" "}" ]
                   ";"
                 / attached-partition-decl

• FOREIGN name ... SERVER s [OPTIONS (...)] makes the partition a foreign table [PG: CREATE FOREIGN TABLE ... PARTITION OF].
• A partition MAY be partitioned again, with its own PARTITION BY and nested PARTITIONS block, to any depth.
• A partition MAY carry a { } body with its own constraints (Section 7.3).
• RENAMED FROM old [DPG] renames a partition already attached to this parent, with a bare name (a partition is in its parent’s schema).

TABLE events ( id BIGINT, region TEXT, payload JSONB, created_at TIMESTAMPTZ )
    PARTITION BY RANGE (created_at)
{
    PARTITIONS {
        events_2024 FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
            PARTITION BY LIST (region) {
                PARTITIONS {
                    events_2024_us FOR VALUES IN ('us-east', 'us-west');
                    events_2024_eu FOR VALUES IN ('eu-west', 'eu-central');
                }
            };
        FOREIGN events_archive DEFAULT
            SERVER archive_server OPTIONS (table_name 'events_archive');
    }
}

Attaching and detaching existing tables [DPG]: to turn an existing standalone table into a partition, or a partition back into a standalone table, without recreating it:

; a PARTITIONS { } entry
attached-partition-decl = "ATTACHED FROM" WSP table-ref WSP
                          <PostgreSQL's partition_bound_spec> ";"

; a standalone TABLE { } block directive
detached-from-dir = "DETACHED FROM" WSP table-ref [ WSP "CONCURRENTLY" ] ";"

• ATTACHED FROM existing_table FOR VALUES ..., where the table is a standalone table in the snapshot, emits ALTER TABLE parent ATTACH PARTITION existing_table FOR VALUES ....
• A standalone TABLE with DETACHED FROM parent [CONCURRENTLY], where the table is a partition of parent in the snapshot, emits ALTER TABLE parent DETACH PARTITION name [CONCURRENTLY] instead of dropping the partition.
• RENAMED FROM cannot be combined with ATTACHED FROM or DETACHED FROM on the same entry; that takes two migrations.

Diffing semantics:

Partition strategy change [DPG]: [PG] a table’s partitioning cannot be altered; it has to be rebuilt:

1. Create the new partitioned table with the target strategy.
2. Create all declared partitions.
3. INSERT INTO new_table SELECT * FROM old_table.
4. Verify row counts match.
5. DROP TABLE old_table.
6. ALTER TABLE new_table RENAME TO old_table.
7. Recreate all indexes, constraints, grants, and policies.

DPG does not run this sequence. It emits one DESTRUCTIVE instruction-only step (Section 17.3) describing it, and dpg apply stops until --approve-partition-rebuild acknowledges it.

Notes [DPG]: an index declared on the parent is created once on the parent; [PG] PostgreSQL creates it on every partition, so DPG emits no per-partition CREATE INDEX. RLS and policies are not propagated to partitions (Section 7.8).

8. Views

8.1. Regular Views

PG equivalent: CREATE VIEW

Part 1: PostgreSQL’s CREATE VIEW without CREATE (and without OR REPLACE, which DPG decides itself). WITH [LOCAL | CASCADED] CHECK OPTION ends Part 1, as in PostgreSQL.

Part 2: OWNER, COMMENT, RENAMED FROM, DEPRECATED, GRANTS, REVOKES, SECURITY LABEL, NAME MAP (Section 4.2.1).

view-block     = *( view-directive ";" )
view-directive = owner-dir / comment-dir / renamed-from-dir
               / deprecated-dir / grants-block / revocations-block
               / security-label-decl

Temporary views [DPG] are excluded on the same terms as temporary tables (Section 7.12): TEMPORARY VIEW is error DPG-E023.

Examples:

SCHEMA public {
    VIEW active_users AS
        SELECT id, email, created_at
        FROM users
        WHERE status = 'active' AND deleted_at IS NULL;

    VIEW user_summary (user_id, email, order_count) AS
        SELECT u.id, u.email, COUNT(o.id)
        FROM users u
        LEFT JOIN orders o ON o.user_id = u.id
        GROUP BY u.id, u.email;

    VIEW secure_view WITH (security_barrier = true) AS
        SELECT id, email FROM users WHERE tenant_id = current_tenant();

    VIEW active_orders AS
        SELECT * FROM orders WHERE status != 'cancelled'
        WITH LOCAL CHECK OPTION;

    VIEW admin_summary AS
        SELECT id, email, created_at FROM users WHERE role = 'admin' {
        COMMENT 'Admin user summary view';
        GRANTS { SELECT TO app_readonly; }
    }
}

Diffing semantics:

Notes:

• [PG] WITH [LOCAL | CASCADED] CHECK OPTION is the view option check_option = local | cascaded, and PostgreSQL stores both spellings the same way, so DPG treats them as one declaration. A view option without a value (WITH (security_barrier)) means true. [PG] CREATE OR REPLACE VIEW resets any option it does not restate.
• [DPG] Output columns. The output column list is compared by name and position. It is known for a plain SELECT (or a set operation, from its leftmost branch) without * in its select list. When it can’t be determined, a query change is emitted as CREATE OR REPLACE VIEW, and PostgreSQL rejects it if the columns did change.
• [DPG] Comparison with the live catalog. [PG] PostgreSQL stores a view’s query with every * expanded and keeps no separate column-name list. When a declared view is compared with one read from the live catalog (dpg verify, dpg plan --live), a declared query whose select list contains * is compared without its select list, and the column-name list is not compared. A change made outside DPG to such a view’s select list is therefore not detected.

8.2. Materialized Views

PG equivalent: CREATE MATERIALIZED VIEW

Part 1: PostgreSQL’s CREATE MATERIALIZED VIEW without CREATE.

Part 2: OWNER, COMMENT, RENAMED FROM, DEPRECATED, GRANTS, REVOKES, SECURITY LABEL, NAME MAP (Section 4.2.1), and INDEXES { } [PG: CREATE INDEX] (Section 7.7).

matview-block     = *( matview-directive ";" )
matview-directive = owner-dir / comment-dir / renamed-from-dir
                  / deprecated-dir / indices-block / grants-block
                  / revocations-block / security-label-decl

Example:

SCHEMA analytics {
    MATERIALIZED VIEW product_stats
    WITH (fillfactor = 90)
    TABLESPACE analytics_space AS
        SELECT product_id, COUNT(*) AS purchases, AVG(price) AS avg_price
        FROM order_items
        GROUP BY product_id
    WITH NO DATA {
        INDEXES { idx_product_stats_id (product_id); }
        GRANTS  { SELECT TO app_readonly; }
    }
}

Diffing semantics:

REFRESH MATERIALIZED VIEW is a runtime operation and out of scope (Section 23).

8.3. Recursive Views

PG equivalent: CREATE RECURSIVE VIEW

Part 1: PostgreSQL’s CREATE RECURSIVE VIEW without CREATE. Part 2: as Section 8.1.

SCHEMA public {
    RECURSIVE VIEW org_tree (id, parent_id, depth, path) AS
        SELECT id, parent_id, 0, ARRAY[id]
        FROM departments WHERE parent_id IS NULL
        UNION ALL
        SELECT d.id, d.parent_id, t.depth + 1, t.path || d.id
        FROM departments d JOIN org_tree t ON d.parent_id = t.id;
}