Skip to content

Schema Reference

Every object in a pglifecycle project is validated against one of these schemas. They are the project format's contract; this reference is generated from them, so it cannot drift from what the tool enforces.

For where each file lives on disk, see Project Format.

Validating in an editor

Each schema is published as resolvable JSON Schema. Point yaml-language-server (VS Code, Neovim, Helix) at the one for the file you are editing to get completion and validation as you type:

# yaml-language-server: $schema=https://gmr.github.io/pglifecycle/schemata/table.json
---
name: users
schema: test

Objects

  • Project — project.yaml at the root of a project: the database-wide settings, and the objects that belong to the database rather than to a schema.
  • Aggregate — An Aggregate function computes a single result from a set of input values.
  • Cast — Defines an cast
  • Collation — A collation is a schema object that provides the locale rules used when sorting and comparing text.
  • Conversion — Defines a conversion between character set encodings
  • Domain — Creates a user-defined data type with optional constraints.
  • Event Trigger — Defines an Event Trigger
  • Foreign Data Wrapper — Foreign Data Wrappers provide a standardized way of handling access to remote data stores.
  • Function — Defines a function
  • Procedure — Defines a procedure (CREATE PROCEDURE), which CALL invokes and which returns no value
  • Index — Defines an index on a table
  • Materialized View — Defines a materialized view
  • Operator — User defined comparison operator
  • Operator Class — An operator class (CREATE OPERATOR CLASS): how an index method uses the operators and support functions of a data type.
  • Operator Family — An operator family (CREATE OPERATOR FAMILY). The operators and functions here are the members that are added to the family with ALTER OPERATOR FAMILY ... ADD. The members of an operator class are in the class.
  • Access Method — A table or index access method (CREATE ACCESS METHOD). An access method has no schema and no owner.
  • Publication — A publication is essentially a group of tables whose data changes are intended to be replicated through logical replication.
  • Schema — A schema is essentially a namespace: it contains named objects (tables, data types, functions, and operators) whose names can duplicate those of other objects existing in other schemas. Named objects are accessed either by “qualifying” their names with the schema name as a prefix, or by setting a search path that includes the desired schema(s).
  • Sequence — Defines a sequence object
  • Statistics — Extended statistics on a table or materialized view (CREATE STATISTICS), which let the planner estimate columns that depend on each other
  • Server — A foreign server typically encapsulates connection information that a foreign-data wrapper uses to access an external data resource.
  • Subscription — The subscription represents a replication connection to the publisher.
  • Table — Defines a table
  • Tablespace — A tablespace allows superusers to define an alternative location on the file system where the data files containing database objects (such as tables and indexes) can reside.
  • Text Search — Defines all properties for text-search in a schema
  • Transform — Defines a transform, which converts a data type to and from the form a procedural language uses. A transform has no owner. The project keeps it in the schema of its data type. When the data type is built-in, the project keeps it in the first schema that its functions name, or in public.
  • Type — User defined data type. here are five forms of types. They respectively create a composite type, an enum type, a range type, a base type, or a shell type. A shell type is simply a placeholder for a type to be defined later. Shell types are needed as forward references when creating range types and base types, as discussed in those sections.
  • View — Defines a view

Roles and privileges

  • ACLs — Privileges on database objects, grouped by the kind of object they are on. Written under grants: or revocations: on the role, user, or group they apply to, so a role file is the whole story of what that role can do. ALL stands for every privilege the object type has. Append WITH GRANT OPTION to a privilege to let the grantee grant it onward. A key names its object as schema.name (schema.table.column for a column), with each name as it is, not quoted: My Schema.My Table. The first period ends the schema name, so a schema name in such a key cannot contain a period. In a column key, the last period starts the column name, so a column name cannot contain a period.
  • Default Privileges — The privileges objects a role creates start with (ALTER DEFAULT PRIVILEGES FOR ROLE). Global and per-schema declarations compose, so they are kept as written.
  • Group — A group is a type of role that defines permissions for one or more users.
  • Role — A role is an entity that can own database objects and have database privileges.
  • User — A user is a role that is permitted to login to the database.
  • User Mapping — A user mapping typically encapsulates connection information that a foreign-data wrapper uses together with the information encapsulated by a foreign server to access an external data resource.

Shared definitions

  • Argument
  • Column — Defines a column in a table
  • Constraint — Defines a constraint
  • Dependencies — Objects this object depends on, for the cases the dependency graph cannot infer: an ordering the topological sort would otherwise get wrong, such as a table that a view reads, or a function that a SQL-standard function body calls. A foreign key needs no entry: the build adds each foreign key after all tables. Each entry names an object as schema.name, or unqualified for a schemaless type. A function or aggregate carries the argument types of one overload (test.f(integer), or test.agg(*) for an aggregate with no arguments), and an operator its left and right argument types (test.=~=(integer, integer)), since overloads share a name, and a cast its types ((int4 AS timestamp)). The argument types compare as PostgreSQL resolves them. A bare function, aggregate or operator name is correct only when one overload has it.
  • Foreign Key — Defines a foreign key on a table
  • Operator Class Function — A support function of an operator class or family: FUNCTION number (left, right) function(arguments).
  • Operator Class Operator — An operator of an operator class or family: OPERATOR strategy name (left, right) [FOR ORDER BY sort_family].
  • Policy — Defines a row-level security policy on a table
  • Rule — Defines a rewrite rule on a table or view (CREATE RULE)
  • Security Labels — The security labels of an object (SECURITY LABEL FOR provider ON ... IS 'label'), as the label of each provider: the key is the name of the label provider, and the value is the label. A label applies only when its provider is loaded in the server, for example with shared_preload_libraries. With no security_labels, the project does not manage the labels of the object, and deploy leaves them as the database has them. With the field, the object has exactly the labels that it lists, thus an empty map removes all of them.
  • Trigger — Defines a trigger on a table or view

Per-schema container files

  • Casts — Specifies all user-defined casts in a schema
  • Conversions — Specifies all conversions in a schema
  • Operators — Specifies all user-defined operators in a schema
  • Operator Classes — Specifies all operator classes in a schema
  • Operator Families — Specifies all operator families in a schema
  • Transforms — Specifies the transforms for the data types in a schema
  • Types — Specifies all user-defined types in a schema