Skip to main content

Syntax

pgschema understands the following CREATE TYPE features:
  • Schema-qualified names: Types can be defined in specific schemas
  • ENUM types: User-defined enumeration types
    • Empty enums (no values)
    • Single-quoted string values
    • Multiple values separated by commas
  • Composite types: Row types with named attributes
    • Multiple attributes with their data types
    • Any valid PostgreSQL data type for attributes
    • Named attributes for structured data
  • Range types: Ranges over a subtype that has a btree operator class
    • Built-in subtypes, and enums or domains defined in the same schema
    • subtype_opclass and collation when they differ from the subtype’s defaults
    • subtype_diff, including a function created in the same migration (the range is created after it)
    • canonical, which PostgreSQL only accepts as a C function, so it is dumped and compared but never created by pgschema
    • multirange_type_name when it differs from the default PostgreSQL derives (timerange → timemultirange, floatspan → floatspan_multirange)
    • The multirange and array types PostgreSQL creates alongside the range are part of it: they are not listed as types of their own, and columns may use them directly

Canonical Format

When generating migration SQL, pgschema produces types in the following canonical format:
Key characteristics of the canonical format:
  • Uses single-line format for empty enums
  • Uses multi-line format with indentation for enums with values
  • Each enum value is on its own indented line for readability
  • No comma after the last enum value
  • Composite types use single-line format with attributes separated by commas
  • For DROP operations: DROP TYPE IF EXISTS type_name RESTRICT;
  • ENUM types can be modified by adding values with ALTER TYPE type_name ADD VALUE 'new_value' AFTER 'existing_value';
  • Range types print multirange_type_name only when it is not the default, and subtype_opclass / collation only when they are not the subtype’s defaults
  • PostgreSQL has no ALTER TYPE for a range’s definition, so a changed range is dropped and recreated (DROP TYPE ... RESTRICT; then CREATE TYPE ... AS RANGE). The DROP fails while a column, domain or composite attribute still uses the range or its multirange; change or drop that use in the same migration.
Note: Composite type modifications in pgschema currently require dropping and recreating the type, as PostgreSQL has limited ALTER TYPE support for composite types and complex dependencies make in-place modifications challenging.