Nullability through joins, COALESCE and aggregates

On this page

The same on TimescaleDB. TimescaleDB runs this example unchanged: the SQL and the Go that sgl generates are identical to the PostgreSQL version. Only dialect: in sgl.yaml changes.

A Go string cannot be NULL, so a field that receives a column which might be NULL has to be able to say so. Seagull makes such a field a pointer, where nil means NULL, and leaves every other field a plain value. The work is in knowing which is which. A column's own NOT NULL is where it starts, but a query can take that guarantee away, with an outer join or an aggregate over no rows, or give it back, with a fallback that is never NULL. Seagull follows the SQL to decide each field, so you do not have to remember which columns to check for nil.

This example rings gulls and records where they were seen. One gull has no nickname and another has never been seen, so both kinds of NULL turn up.

The schema

nickname has no NOT NULL, so it may be NULL. Every other column is NOT NULL.

db/migrations/0001_gulls.up.sql

CREATE TABLE gulls (
    id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ring     TEXT   NOT NULL UNIQUE,
    nickname TEXT
);

CREATE TABLE sightings (
    id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    gull_id BIGINT NOT NULL REFERENCES gulls (id),
    seen_on DATE   NOT NULL,
    place   TEXT   NOT NULL
);

A nullable column

RingGull inserts a gull. Because nickname may be NULL, its parameter is a *string, and the program passes nil for the gull without one.

internal/store

type RingGullParams struct {
	Ring     string
	Nickname *string
}

func (queries *Queries) RingGull(ctx context.Context, params RingGullParams) error {
	_, err := queries.writer.ExecContext(ctx, ringGullSQL, params.Ring, params.Nickname)
	return err
}

Through a LEFT JOIN

ListSightings lists every gull with each of its sightings. sightings.place cannot be NULL in its own table, but a LEFT JOIN keeps a gull that has no sightings and gives it NULL for every sighting column. So in this query Place is a *string too, alongside Nickname, which was nullable from the start.

db/queries/gulls.sql

-- sgl.query(ListSightings, many)
SELECT gulls.ring, gulls.nickname, sightings.place
FROM gulls
LEFT JOIN sightings ON sightings.gull_id = gulls.id
ORDER BY gulls.id, sightings.seen_on;

internal/store

type ListSightingsRow struct {
	Ring     string  `json:"ring"`
	Nickname *string `json:"nickname"`
	Place    *string `json:"place"`
}

func (queries *Queries) ListSightings(ctx context.Context) ([]ListSightingsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listSightingsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListSightingsRow
	for rows.Next() {
		var row ListSightingsRow
		if err := rows.Scan(&row.Ring, &row.Nickname, &row.Place); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

COALESCE and aggregates

SummariseGulls gives one line per gull: what to call it, how often it was seen, and when it was last seen. Each of those three columns comes out differently.

db/queries/gulls.sql

-- sgl.query(SummariseGulls, many)
SELECT
    gulls.ring,
    COALESCE(gulls.nickname, 'ring ' || gulls.ring) AS known_as,
    COUNT(sightings.id) AS times_seen,
    MAX(sightings.seen_on) AS last_seen
FROM gulls
LEFT JOIN sightings ON sightings.gull_id = gulls.id
GROUP BY gulls.id
ORDER BY gulls.id;
  • COALESCE falls back to 'ring ' || gulls.ring, which is never NULL, so KnownAs is a plain string.
  • COUNT is never NULL, since no rows counts as 0, so TimesSeen is a plain int64.
  • MAX over no rows is NULL, so LastSeen is a pointer. It keeps the column's type, and seen_on is a DATE, so it is a *time.Time.

internal/store

type SummariseGullsRow struct {
	Ring      string     `json:"ring"`
	KnownAs   string     `json:"known_as"`
	TimesSeen int64      `json:"times_seen"`
	LastSeen  *time.Time `json:"last_seen"`
}

func (queries *Queries) SummariseGulls(ctx context.Context) ([]SummariseGullsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, summariseGullsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []SummariseGullsRow
	for rows.Next() {
		var row SummariseGullsRow
		if err := rows.Scan(&row.Ring, &row.KnownAs, &row.TimesSeen, &row.LastSeen); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

Using it

The program reads every pointer through a small orNull helper, which prints NULL for nil. The types say which values can be missing: row.Ring is a string to print as it is, while row.Place has to be dereferenced, and may be nil.

example.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"io"
	"time"

	"seagull.sh/seagull/examples/postgres/004_nullability/internal/store"
)

// run rings three gulls, records where two of them were seen, and lists them twice.
// Seagull decides each field's nullability from the SQL, and a field that can be NULL is
// a pointer:
//
//   - gulls.nickname has no NOT NULL, so Nickname is *string, as a parameter and a
//     column;
//   - sightings.place is NOT NULL, but the LEFT JOIN gives a gull with no sightings a
//     NULL place, so Place is *string;
//   - COALESCE with a non-NULL last argument is never NULL, so KnownAs is string;
//   - COUNT is never NULL, but MAX over no rows is, so TimesSeen is int64 and LastSeen is
//     *time.Time, keeping the DATE column's type.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives both listings.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	gulls := []store.RingGullParams{
		{Ring: "GB1001", Nickname: new("Steven")},
		{Ring: "GB1002", Nickname: nil},
		{Ring: "GB1003", Nickname: new("Gwen")},
	}
	for _, gull := range gulls {
		if err := queries.RingGull(ctx, gull); err != nil {
			return fmt.Errorf("ringing %s: %w", gull.Ring, err)
		}
	}
	sightings := []store.RecordSightingParams{
		{GullID: 1, SeenOn: mayDay(1), Place: "the pier"},
		{GullID: 1, SeenOn: mayDay(3), Place: "the chip shop"},
		{GullID: 2, SeenOn: mayDay(2), Place: "the harbour wall"},
	}
	for _, sighting := range sightings {
		if err := queries.RecordSighting(ctx, sighting); err != nil {
			return fmt.Errorf("recording a sighting: %w", err)
		}
	}

	rows, err := queries.ListSightings(ctx)
	if err != nil {
		return fmt.Errorf("listing sightings: %w", err)
	}
	fmt.Fprintln(output, "ring    nickname  place")
	for _, row := range rows {
		fmt.Fprintf(output, "%-7s %-9s %s\n", row.Ring, orNull(row.Nickname), orNull(row.Place))
	}

	summaries, err := queries.SummariseGulls(ctx)
	if err != nil {
		return fmt.Errorf("summarising gulls: %w", err)
	}
	fmt.Fprintln(output, "\nring    known as     times seen  last seen")
	for _, summary := range summaries {
		lastSeen := "NULL"
		if summary.LastSeen != nil {
			lastSeen = summary.LastSeen.Format(time.DateOnly)
		}
		fmt.Fprintf(output, "%-7s %-12s %-11d %s\n", summary.Ring, summary.KnownAs, summary.TimesSeen, lastSeen)
	}
	return nil
}

// mayDay returns a day in May 2026, for the DATE column.
//
// Takes day (int) which is the day of the month.
//
// Returns time.Time which is midnight UTC on that day.
func mayDay(day int) time.Time {
	return time.Date(2026, time.May, day, 0, 0, 0, 0, time.UTC)
}

// orNull returns the text a nullable field holds, or NULL when the field is nil.
//
// Takes value (*string) which is a nullable field.
//
// Returns string which is the text to print.
func orNull(value *string) string {
	if value == nil {
		return "NULL"
	}
	return *value
}

What it prints

ring    nickname  place
GB1001  Steven    the pier
GB1001  Steven    the chip shop
GB1002  NULL      the harbour wall
GB1003  Gwen      NULL

ring    known as     times seen  last seen
GB1001  Steven       2           2026-05-03
GB1002  ring GB1002  1           2026-05-02
GB1003  Gwen         0           NULL

Try it

Change the LEFT JOIN in ListSightings to a plain JOIN and run sgl generate. Place becomes a string, because an inner join never invents a row, and the program prints one line fewer: the gull that was never seen drops out.

Inference follows the SQL, not your data. When you know a column cannot be NULL in practice, sgl.column(name, nullable: false) in the query's header overrides it for that query; Type overrides covers the directive.

Across engines

Only these Go types of SummariseGulls differ between engines; the rest are the same everywhere.

PostgreSQLSQLiteMySQLDuckDBClickHouse
times_seen columnint64int64int64int64uint64
last_seen column*time.Time*string*time.Time*time.Time*time.Time

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

MariaDB runs the MySQL version unchanged.

Compare the whole example side by side with SQLite, MySQL, DuckDB and ClickHouse.

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/postgres && go run ./004_nullability

Its test runs it too, and checks what it prints against output.txt:

cd seagull/examples/postgres && SGL_EXAMPLE_ENGINE=timescaledb SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./004_nullability

Needs a running TimescaleDB server: set DATABASE_URL, or pass -dsn, to the address of an empty database.

The files

Every file of the example, as it is in the Seagull repository.

sgl.yaml

# Copyright 2026 PolitePixels Limited
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

# This project stands against fascism, authoritarianism, and all forms of
# oppression. We built this to empower people, not to enable those who would
# strip others of their rights and dignity.

version: 1

databases:
  - name: colony
    dialect: postgres
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store
      driver: database/sql

db/migrations/0001_gulls.up.sql

CREATE TABLE gulls (
    id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ring     TEXT   NOT NULL UNIQUE,
    nickname TEXT
);

CREATE TABLE sightings (
    id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    gull_id BIGINT NOT NULL REFERENCES gulls (id),
    seen_on DATE   NOT NULL,
    place   TEXT   NOT NULL
);

db/migrations/0001_gulls.down.sql

DROP TABLE sightings;
DROP TABLE gulls;

db/queries/gulls.sql

-- sgl.query(RingGull, exec)
INSERT INTO gulls (ring, nickname) VALUES ($1, $2);

-- sgl.query(RecordSighting, exec)
INSERT INTO sightings (gull_id, seen_on, place) VALUES ($1, $2, $3);

-- sgl.query(ListSightings, many)
SELECT gulls.ring, gulls.nickname, sightings.place
FROM gulls
LEFT JOIN sightings ON sightings.gull_id = gulls.id
ORDER BY gulls.id, sightings.seen_on;

-- sgl.query(SummariseGulls, many)
SELECT
    gulls.ring,
    COALESCE(gulls.nickname, 'ring ' || gulls.ring) AS known_as,
    COUNT(sightings.id) AS times_seen,
    MAX(sightings.seen_on) AS last_seen
FROM gulls
LEFT JOIN sightings ON sightings.gull_id = gulls.id
GROUP BY gulls.id
ORDER BY gulls.id;

example.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"io"
	"time"

	"seagull.sh/seagull/examples/postgres/004_nullability/internal/store"
)

// run rings three gulls, records where two of them were seen, and lists them twice.
// Seagull decides each field's nullability from the SQL, and a field that can be NULL is
// a pointer:
//
//   - gulls.nickname has no NOT NULL, so Nickname is *string, as a parameter and a
//     column;
//   - sightings.place is NOT NULL, but the LEFT JOIN gives a gull with no sightings a
//     NULL place, so Place is *string;
//   - COALESCE with a non-NULL last argument is never NULL, so KnownAs is string;
//   - COUNT is never NULL, but MAX over no rows is, so TimesSeen is int64 and LastSeen is
//     *time.Time, keeping the DATE column's type.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives both listings.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	gulls := []store.RingGullParams{
		{Ring: "GB1001", Nickname: new("Steven")},
		{Ring: "GB1002", Nickname: nil},
		{Ring: "GB1003", Nickname: new("Gwen")},
	}
	for _, gull := range gulls {
		if err := queries.RingGull(ctx, gull); err != nil {
			return fmt.Errorf("ringing %s: %w", gull.Ring, err)
		}
	}
	sightings := []store.RecordSightingParams{
		{GullID: 1, SeenOn: mayDay(1), Place: "the pier"},
		{GullID: 1, SeenOn: mayDay(3), Place: "the chip shop"},
		{GullID: 2, SeenOn: mayDay(2), Place: "the harbour wall"},
	}
	for _, sighting := range sightings {
		if err := queries.RecordSighting(ctx, sighting); err != nil {
			return fmt.Errorf("recording a sighting: %w", err)
		}
	}

	rows, err := queries.ListSightings(ctx)
	if err != nil {
		return fmt.Errorf("listing sightings: %w", err)
	}
	fmt.Fprintln(output, "ring    nickname  place")
	for _, row := range rows {
		fmt.Fprintf(output, "%-7s %-9s %s\n", row.Ring, orNull(row.Nickname), orNull(row.Place))
	}

	summaries, err := queries.SummariseGulls(ctx)
	if err != nil {
		return fmt.Errorf("summarising gulls: %w", err)
	}
	fmt.Fprintln(output, "\nring    known as     times seen  last seen")
	for _, summary := range summaries {
		lastSeen := "NULL"
		if summary.LastSeen != nil {
			lastSeen = summary.LastSeen.Format(time.DateOnly)
		}
		fmt.Fprintf(output, "%-7s %-12s %-11d %s\n", summary.Ring, summary.KnownAs, summary.TimesSeen, lastSeen)
	}
	return nil
}

// mayDay returns a day in May 2026, for the DATE column.
//
// Takes day (int) which is the day of the month.
//
// Returns time.Time which is midnight UTC on that day.
func mayDay(day int) time.Time {
	return time.Date(2026, time.May, day, 0, 0, 0, 0, time.UTC)
}

// orNull returns the text a nullable field holds, or NULL when the field is nil.
//
// Takes value (*string) which is a nullable field.
//
// Returns string which is the text to print.
func orNull(value *string) string {
	if value == nil {
		return "NULL"
	}
	return *value
}

main.go

// Command 004_nullability applies its migrations to a PostgreSQL database, then runs the
// example in example.go: three gulls are ringed, their sightings recorded, and both
// listings print NULL where a pointer is nil.
//
// Run it with `go run . -dsn postgres://user:password@localhost:5432/example`, or set
// DATABASE_URL instead of passing -dsn. Give it an empty database of its own, as the
// migrations create its tables and the example adds rows each time it runs.
package main

import (
	"context"
	"database/sql"
	"embed"
	"errors"
	"flag"
	"fmt"
	"log"
	"os"

	_ "github.com/jackc/pgx/v5/stdlib"

	"seagull.sh/seagull/sdk/migrate"
	"seagull.sh/seagull/sdk/migrate/migrate_backend_postgres"
)

// migrations holds the migration files, compiled into the binary.
//
//go:embed db/migrations
var migrations embed.FS

func main() {
	dsn := flag.String("dsn", os.Getenv("DATABASE_URL"), "PostgreSQL connection string; defaults to $DATABASE_URL")
	flag.Parse()
	if err := execute(context.Background(), *dsn); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the database at dsn, and runs the example against it.
//
// Takes dsn (string) which is the PostgreSQL connection string.
//
// Returns error when the database cannot be prepared or the example fails.
func execute(ctx context.Context, dsn string) error {
	if dsn == "" {
		return errors.New("set -dsn or DATABASE_URL to a PostgreSQL connection string")
	}
	database, err := sql.Open("pgx", dsn)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_postgres.New(), migrations, "db/migrations")
	if err != nil {
		return err
	}
	if _, err := migrator.Up(ctx); err != nil {
		return fmt.Errorf("migrating: %w", err)
	}
	return run(ctx, database, os.Stdout)
}

internal/store/gulls.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"time"
)

const listSightingsSQL = `SELECT gulls.ring, gulls.nickname, sightings.place
FROM gulls
LEFT JOIN sightings ON sightings.gull_id = gulls.id
ORDER BY gulls.id, sightings.seen_on;`

type ListSightingsRow struct {
	Ring     string  `json:"ring"`
	Nickname *string `json:"nickname"`
	Place    *string `json:"place"`
}

func (queries *Queries) ListSightings(ctx context.Context) ([]ListSightingsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listSightingsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListSightingsRow
	for rows.Next() {
		var row ListSightingsRow
		if err := rows.Scan(&row.Ring, &row.Nickname, &row.Place); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const recordSightingSQL = `INSERT INTO sightings (gull_id, seen_on, place) VALUES ($1, $2, $3);`

type RecordSightingParams struct {
	GullID int64
	SeenOn time.Time
	Place  string
}

func (queries *Queries) RecordSighting(ctx context.Context, params RecordSightingParams) error {
	_, err := queries.writer.ExecContext(ctx, recordSightingSQL, params.GullID, params.SeenOn, params.Place)
	return err
}

const ringGullSQL = `INSERT INTO gulls (ring, nickname) VALUES ($1, $2);`

type RingGullParams struct {
	Ring     string
	Nickname *string
}

func (queries *Queries) RingGull(ctx context.Context, params RingGullParams) error {
	_, err := queries.writer.ExecContext(ctx, ringGullSQL, params.Ring, params.Nickname)
	return err
}

const summariseGullsSQL = `SELECT
    gulls.ring,
    COALESCE(gulls.nickname, 'ring ' || gulls.ring) AS known_as,
    COUNT(sightings.id) AS times_seen,
    MAX(sightings.seen_on) AS last_seen
FROM gulls
LEFT JOIN sightings ON sightings.gull_id = gulls.id
GROUP BY gulls.id
ORDER BY gulls.id;`

type SummariseGullsRow struct {
	Ring      string     `json:"ring"`
	KnownAs   string     `json:"known_as"`
	TimesSeen int64      `json:"times_seen"`
	LastSeen  *time.Time `json:"last_seen"`
}

func (queries *Queries) SummariseGulls(ctx context.Context) ([]SummariseGullsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, summariseGullsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []SummariseGullsRow
	for rows.Next() {
		var row SummariseGullsRow
		if err := rows.Scan(&row.Ring, &row.KnownAs, &row.TimesSeen, &row.LastSeen); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

internal/store/models.go

// Code generated by sgl. DO NOT EDIT.

package store

import "time"

type Gulls struct {
	ID       int64   `json:"id"`
	Ring     string  `json:"ring"`
	Nickname *string `json:"nickname"`
}

type Sightings struct {
	ID     int64     `json:"id"`
	GullID int64     `json:"gull_id"`
	SeenOn time.Time `json:"seen_on"`
	Place  string    `json:"place"`
}

internal/store/querier.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"database/sql"
)

type DBTX interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

type dbtxWrapper interface {
	WrapDBTX(inner any) any
}

type Queries struct {
	reader  DBTX
	writer  DBTX
	wrapper dbtxWrapper
}

func New(db DBTX) *Queries {
	wrapper, _ := db.(dbtxWrapper)
	return &Queries{reader: db, writer: db, wrapper: wrapper}
}

func NewWithReplica(writer DBTX, reader DBTX) *Queries {
	wrapper, _ := writer.(dbtxWrapper)
	return &Queries{reader: reader, writer: writer, wrapper: wrapper}
}

func (queries *Queries) WithTx(transaction *sql.Tx) *Queries {
	if queries == nil {
		return &Queries{reader: transaction, writer: transaction, wrapper: nil}
	}
	db := queries.wrapDBTX(transaction)
	return &Queries{reader: db, writer: db, wrapper: queries.wrapper}
}

func (queries *Queries) wrapDBTX(db DBTX) DBTX {
	if queries.wrapper == nil {
		return db
	}
	wrapped, ok := queries.wrapper.WrapDBTX(db).(DBTX)
	if !ok {
		return db
	}
	return wrapped
}

func (queries *Queries) RunInTx(ctx context.Context, db *sql.DB, fn func(*Queries) error) error {
	transaction, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer transaction.Rollback()
	if err = fn(queries.WithTx(transaction)); err != nil {
		return err
	}
	return transaction.Commit()
}

output.txt

ring    nickname  place
GB1001  Steven    the pier
GB1001  Steven    the chip shop
GB1002  NULL      the harbour wall
GB1003  Gwen      NULL

ring    known as     times seen  last seen
GB1001  Steven       2           2026-05-03
GB1002  ring GB1002  1           2026-05-02
GB1003  Gwen         0           NULL

Also on other engines

SQLiteRinged gulls and their sightings, where a nullable column, a LEFT JOIN and MAX give pointers while COALESCE and COUNT do not.PostgreSQLRinged gulls and their sightings, where a nullable column, a LEFT JOIN and MAX give pointers while COALESCE and COUNT do not.CockroachDBRinged gulls and their sightings, where a nullable column, a LEFT JOIN and MAX give pointers while COALESCE and COUNT do not.MySQLRinged gulls and their sightings, where a nullable column, a LEFT JOIN and MAX give pointers while COALESCE and COUNT do not.MariaDBRinged gulls and their sightings, where a nullable column, a LEFT JOIN and MAX give pointers while COALESCE and COUNT do not.DuckDBRinged gulls and their sightings, where a nullable column, a LEFT JOIN and MAX give pointers while COALESCE and COUNT do not.ClickHouseRinged gulls and their sightings, where Nullable(String) gives a pointer and join_use_nulls decides whether a LEFT JOIN's unmatched row is NULL or each column's default.