sql-yodeler-example-postgres

Postgres, 5 objects, from chant build of the declared schema. Written by yodel docs.

Objects

shop Schema
shop.orders Table
shop.orders_placed_at_idx Index
shop.orders_refunded_at_idx Index
shop.orders_status_idx Index

Entity relationships

Mermaid source; erd.md next to this file renders it on GitHub, GitLab and Forgejo.

erDiagram
  shop_orders["shop.orders"] {
    bigint id PK
    text email
    text status
    numeric(12_2) amount
    timestamp_with_time_zone placed_at
    timestamp_with_time_zone refunded_at
    boolean gift
  }

Reference

shopPostgres::Schema, export shopSchema

The SQL Yodeler Postgres example

History

Migration	Step
`20261010T1723-baseline`	`SQLPG200 create: CREATE SCHEMA shop`
`20261010T1723-baseline`	`SQLPG200 create: COMMENT ON SCHEMA shop IS 'The SQL Yodeler Postgres example [chant managed-by=chant]'`

DDL

CREATE SCHEMA shop;
  COMMENT ON SCHEMA shop IS 'The SQL Yodeler Postgres example'

shop.ordersPostgres::Table, export orders

Primary key: (id) orders_pkey
Check: amount > 0 orders_amount_positive

Column	Type	Null	Key	Default
`id`	`bigint`	yes	PK	GENERATED ALWAYS AS IDENTITY
`email`	`text`	no
`status`	`text`	no		DEFAULT 'placed'::text
`amount`	`numeric(12,2)`	no
`placed_at`	`timestamp with time zone`	no		DEFAULT now()
`refunded_at`	`timestamp with time zone`	yes
`gift`	`boolean`	no		DEFAULT false

History

Migration	Step
`20261010T1723-baseline`	`SQLPG200 create: CREATE TABLE shop.orders ( id bigint GENERATED ALWAYS AS IDENTITY, customer_email text NOT NULL, status text DEFAULT 'placed'::text NOT NULL, amount numeric(12,2) NOT NULL, placed_at timestamp with time zone DEFAULT now() NOT NULL, CONSTRAINT orders_pkey PRIMARY KEY (id) )`
`20261010T1723-baseline`	`SQLPG200 create: COMMENT ON TABLE shop.orders IS '[chant managed-by=chant]'`
`20261010T1723-coupons`	`SQLPG201 metadata: ALTER TABLE shop.orders ADD COLUMN coupon text`
`20261010T1723-coupons`	`SQLPG217 metadata: ALTER TABLE shop.orders ADD CONSTRAINT orders_amount_positive CHECK (amount > 0) NOT VALID`
`20261010T1723-coupons`	`SQLPG220 validate: ALTER TABLE shop.orders VALIDATE CONSTRAINT orders_amount_positive`
`20261010T1724-refunds`	`SQLPG201 metadata: ALTER TABLE shop.orders ADD COLUMN refunded_at timestamp with time zone`
`20261010T1724-rename-email`	`PostgresMigrationOp step (SQLPG205 columns.email)`
`20261010T1724-drop-coupon`	`SQLPG204 metadata: ALTER TABLE shop.orders DROP COLUMN coupon`
`20261010T1725-add-gift`	`SQLPG201 metadata: ALTER TABLE shop.orders ADD COLUMN gift boolean DEFAULT false NOT NULL`

DDL

CREATE TABLE shop.orders (
      id bigint GENERATED ALWAYS AS IDENTITY,
      email text NOT NULL, -- previously: customer_email
      status text DEFAULT 'placed'::text NOT NULL,
      amount numeric(12,2) NOT NULL,
      placed_at timestamp with time zone DEFAULT now() NOT NULL,
      refunded_at timestamp with time zone,
      gift boolean DEFAULT false NOT NULL,
      CONSTRAINT orders_pkey PRIMARY KEY (id),
      CONSTRAINT orders_amount_positive CHECK (amount > 0)
  )

shop.orders_placed_at_idxPostgres::Index, export ordersPlacedAt

On: shop.orders (placed_at)

History

Migration	Step
`20261010T1725-index-placed-at`	`SQLPG240 concurrently: CREATE INDEX CONCURRENTLY orders_placed_at_idx ON shop.orders (placed_at)`
`20261010T1725-index-placed-at`	`SQLPG240 concurrently: COMMENT ON INDEX shop.orders_placed_at_idx IS '[chant managed-by=chant]'`

DDL

CREATE INDEX CONCURRENTLY orders_placed_at_idx ON shop.orders (placed_at)

shop.orders_refunded_at_idxPostgres::Index, export ordersRefundedAt

On: shop.orders (refunded_at)

History

Migration	Step
`20261010T1724-refunds`	`SQLPG240 concurrently: CREATE INDEX CONCURRENTLY orders_refunded_at_idx ON shop.orders (refunded_at)`
`20261010T1724-refunds`	`SQLPG240 concurrently: COMMENT ON INDEX shop.orders_refunded_at_idx IS '[chant managed-by=chant]'`

DDL

CREATE INDEX CONCURRENTLY orders_refunded_at_idx ON shop.orders (refunded_at)

shop.orders_status_idxPostgres::Index, export ordersStatusIdx

On: shop.orders (status) using btree

History

Migration	Step
`20261010T1723-baseline`	`SQLPG200 create: CREATE INDEX orders_status_idx ON shop.orders USING btree (status)`
`20261010T1723-baseline`	`SQLPG200 create: COMMENT ON INDEX shop.orders_status_idx IS '[chant managed-by=chant]'`

DDL

CREATE INDEX orders_status_idx ON shop.orders USING btree (status)