Examples for

PostgreSQL

30 examples, in learning order. The reference dialect, with extension hooks for flavours such as TimescaleDB.

Start at 001: Your first query
Parameters
$1 or :name
RETURNING
Supported
Go code for
database/sql, pgx
Migrations
Built in (PostgreSQL)
Driver
pgx
All about the PostgreSQL dialect

Getting started

4 examples
  1. 001

    Your first query

    A tasks table and three queries through pgx's database/sql driver; BIGSERIAL becomes int64, TIMESTAMPTZ becomes time.Time and RETURNING hands back the new row.

    database/sql and pgx

Types and nullability

5 examples
  1. 005

    From SQL types to Go types

    One landing of fish read back column by column, printing the Go type each PostgreSQL type became, from apd.Decimal and uuid.UUID to an unmapped INET as any.

    database/sql and pgx
  2. 006

    JSON columns

    Chip shop orders kept as JSONB documents, encoded into the generated JSON type and searched with ->, ->> and @>.

    database/sql and pgx
  4. 008

    Arrays and lists as typed slices

    Fishing voyages with TEXT[] and BIGINT[] columns read back as typed Go slices, searched with ANY and grouped with array_agg.

    database/sql and pgx
  5. 009

    Type overrides

    A chip shop menu read back as its own Status and Price types, through the types.decimal slot, a column override in sgl.yaml and sgl.column in a migration and in queries.

Dynamic queries

5 examples
  1. 010

    IN lists from Go slices

    Beach huts found and booked by passing Go slices to IN lists, including an empty slice and a slice after a scalar.

    database/sql and pgx
  2. 011

    Optional filters

    One boat trip search run four ways, where each optional filter is a pointer field and a nil pointer switches its filter off.

  3. 012

    Validated ORDER BY

    Regatta entries listed in the order a caller picks from generated constants, with raw text adding no ORDER BY at all.

  5. 014

    The runtime query builder

    Unclaimed lost property searched with filters, an order, a limit and a count chosen at run time, with unsafe columns, operators and directions refused.

Shaping results

5 examples
  1. 015

    Nested structs with sgl.embed

    Boats read with their harbour as a nested value through an INNER JOIN and their skipper as a nil-able pointer through a LEFT JOIN.

  2. 016

    Parents with child slices

    Chip shop orders collapsed from a LEFT JOIN into one row each, with their lines as a slice, by group_by; the same query and output as SQLite.

  3. 017

    Range over rows

    Tide gauge readings ranged over one row at a time with a range-over-func iterator, once to the end and once stopping early, as on SQLite.

  4. 018

    Missing rows without errors

    Gulls ringed with INSERT ... ON CONFLICT DO NOTHING RETURNING and looked up by ring, both reporting a missing row as ok false; the skipped insert still uses up an id.

  5. 019

    Types through CTEs, window functions and views

    A gull colony read through a recursive CTE, a CTE feeding RANK and SUM window functions, and a view, with types and nullability carried through each; SUM of a bigint arrives as a decimal.

Writing data

3 examples
  2. 021

    Insert or update

    INSERT ... ON CONFLICT DO UPDATE adds to an existing row through EXCLUDED and returns the result; DO NOTHING with execrows reports whether the insert happened.

  3. 022

    Batch and copyfrom

    A batch query takes a slice of Params and writes it as multi-row INSERTs, 16383 four-column rows a statement to stay under PostgreSQL's 65535 bind variables.

    database/sql and pgx

Connections and transactions

3 examples
  1. 023

    Transactions

    RunInTx commits a transfer when its function returns nil and rolls it back on an error, and WithTx binds the queries to a transaction you begin yourself.

    database/sql and pgx
  2. 024

    Read replicas

    NewWithReplica sends each query to the writer or the reader by Seagull's read-only detection, which on PostgreSQL also catches FOR UPDATE, writing CTEs and calls of functions that change data; a pool with default_transaction_read_only plays the replica.

  3. 025

    Prepared statements

    go.prepared generates Prepare, which prepares every query once at start-up and returns a PreparedDBTX that store.New runs them through.

Migrations and seeds

3 examples
  1. 027

    Migrations at start-up

    The migration files are embedded in the binary, and each start-up runs migrate.NewMigrator with the PostgreSQL backend and Up, which applies only what the database lacks.

  2. 028

    The migration lifecycle

    UpTo, Up, Down, Status and Validate on an embedded set of migrations, then the advisory lock and the PgBouncer table lock that stop two instances migrating at once, refused at once with WithNonBlockingLock.

  3. 029

    Seeds, namespaces and hooks

    A namespaced migrator with before-run and after-migration hooks and a logger, an index built CONCURRENTLY in a migration that runs outside a transaction, and a Seeder that applies each seed file once and warns when one changes.

Engine features

2 examples
  1. 030

    Functions in migrations

    SQL and PL/pgSQL functions created in a migration type the queries that call them, and whether they change data, read from their bodies or stated by a sgl.migration(readonly:) line, decides whether each query goes to the writer or the reader.

  2. 031

    PostgreSQL power queries

    A harbour's landings read with DISTINCT ON, a LEFT JOIN LATERAL subquery, full-text search over a generated tsvector, and jsonb_each, then archived by a DELETE inside a CTE feeding an INSERT, every result typed.

Show 23 not available for PostgreSQL

Some ideas belong to other engines, and some need a feature PostgreSQL does not have. Each one says why, and where to read it instead.