Examples for

TimescaleDB

30 examples, in learning order. PostgreSQL plus hypertables, continuous aggregates and Timescale's functions.

Seagull generates the same code for TimescaleDB as for PostgreSQL, so most pages here show the PostgreSQL files, tested against 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 TimescaleDB 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.

    Same code as PostgreSQL

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.

    Same code as PostgreSQL
  2. 006

    JSON columns

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

    Same code as PostgreSQL
  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.

    Same code as PostgreSQL
  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.

    Same code as PostgreSQL

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.

    Same code as PostgreSQL
  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.

    Same code as PostgreSQL
  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.

    Same code as PostgreSQL
  4. 013

    Pagination with defaults and limits

    Rock pool finds read a page at a time, with a default and a maximum page size, then searched with an optional filter and a page.

    Same code as PostgreSQL
  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.

    Same code as PostgreSQL

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.

    Same code as PostgreSQL
  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.

    Same code as PostgreSQL
  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.

    Same code as PostgreSQL
  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.

    Same code as PostgreSQL
  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.

    Same code as PostgreSQL

Writing data

3 examples
  1. 020

    Rows back from INSERT, UPDATE and DELETE

    INSERT, UPDATE and DELETE with RETURNING, declared as one or many, hand back the rows they wrote, defaults and computed values included.

    Same code as PostgreSQL
  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.

    Same code as PostgreSQL
  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.

    Same code as PostgreSQL

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.

    Same code as PostgreSQL
  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.

    Same code as PostgreSQL
  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.

    Same code as PostgreSQL

Migrations and seeds

1 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.

    Same code as PostgreSQL

Engine features

4 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.

    Same code as PostgreSQL
  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.

    Same code as PostgreSQL
  3. 032

    Hypertables and time buckets

    A migration turns readings into a hypertable with create_hypertable; time_bucket, time_bucket_gapfill with locf, and first and last summarise two buoys' sea temperatures with typed rows.

  4. 033

    Continuous aggregates

    A continuous aggregate keeps a stats_agg summary per buoy per hour; it is created outside a transaction with sgl.migration(no_transaction), refreshed outside RunInTx, read as typed rows, and rolled up over the night.

Show 23 not available for TimescaleDB

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