Compare · 004

Nullability through joins, COALESCE and aggregates: PostgreSQL and CockroachDB

PostgreSQL and CockroachDB run the same files for this example: the SQL and the Go are identical.

Compare PostgreSQL withSQLite CockroachDB TimescaleDB MySQL MariaDB DuckDB ClickHouse

Configuration

sgl.yamlidentical

PostgreSQL
CockroachDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: colony
4  - name: colony
5    dialect: postgres
5    dialect: postgres
6    migrations: db/migrations
6    migrations: db/migrations
7    queries: db/queries
7    queries: db/queries
8    go:
8    go:
9      package: store
9      package: store
10      out: internal/store
10      out: internal/store
11      driver: database/sql
11      driver: database/sql

Migrations

db/migrations/0001_gulls.up.sqlidentical

PostgreSQL
CockroachDB
1CREATE TABLE gulls (
1CREATE TABLE gulls (
2    id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
2    id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
3    ring     TEXT   NOT NULL UNIQUE,
3    ring     TEXT   NOT NULL UNIQUE,
4    nickname TEXT
4    nickname TEXT
5);
5);
6
6
7CREATE TABLE sightings (
7CREATE TABLE sightings (
8    id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
8    id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
9    gull_id BIGINT NOT NULL REFERENCES gulls (id),
9    gull_id BIGINT NOT NULL REFERENCES gulls (id),
10    seen_on DATE   NOT NULL,
10    seen_on DATE   NOT NULL,
11    place   TEXT   NOT NULL
11    place   TEXT   NOT NULL
12);
12);

db/migrations/0001_gulls.down.sqlidentical

PostgreSQL
CockroachDB
1DROP TABLE sightings;
1DROP TABLE sightings;
2DROP TABLE gulls;
2DROP TABLE gulls;

Queries

RingGullidentical

PostgreSQL · db/queries/gulls.sql
CockroachDB · db/queries/gulls.sql
1-- sgl.query(RingGull, exec)
1-- sgl.query(RingGull, exec)
2INSERT INTO gulls (ring, nickname) VALUES ($1, $2);
2INSERT INTO gulls (ring, nickname) VALUES ($1, $2);

RecordSightingidentical

PostgreSQL · db/queries/gulls.sql
CockroachDB · db/queries/gulls.sql
1-- sgl.query(RecordSighting, exec)
1-- sgl.query(RecordSighting, exec)
2INSERT INTO sightings (gull_id, seen_on, place) VALUES ($1, $2, $3);
2INSERT INTO sightings (gull_id, seen_on, place) VALUES ($1, $2, $3);

ListSightingsidentical

PostgreSQL · db/queries/gulls.sql
CockroachDB · db/queries/gulls.sql
1-- sgl.query(ListSightings, many)
1-- sgl.query(ListSightings, many)
2SELECT gulls.ring, gulls.nickname, sightings.place
2SELECT gulls.ring, gulls.nickname, sightings.place
3FROM gulls
3FROM gulls
4LEFT JOIN sightings ON sightings.gull_id = gulls.id
4LEFT JOIN sightings ON sightings.gull_id = gulls.id
5ORDER BY gulls.id, sightings.seen_on;
5ORDER BY gulls.id, sightings.seen_on;

SummariseGullsidentical

PostgreSQL · db/queries/gulls.sql
CockroachDB · db/queries/gulls.sql
1-- sgl.query(SummariseGulls, many)
1-- sgl.query(SummariseGulls, many)
2SELECT
2SELECT
3    gulls.ring,
3    gulls.ring,
4    COALESCE(gulls.nickname, 'ring ' || gulls.ring) AS known_as,
4    COALESCE(gulls.nickname, 'ring ' || gulls.ring) AS known_as,
5    COUNT(sightings.id) AS times_seen,
5    COUNT(sightings.id) AS times_seen,
6    MAX(sightings.seen_on) AS last_seen
6    MAX(sightings.seen_on) AS last_seen
7FROM gulls
7FROM gulls
8LEFT JOIN sightings ON sightings.gull_id = gulls.id
8LEFT JOIN sightings ON sightings.gull_id = gulls.id
9GROUP BY gulls.id
9GROUP BY gulls.id
10ORDER BY gulls.id;
10ORDER BY gulls.id;

What sgl generates

RingGullidentical

PostgreSQL
CockroachDB
1func (queries *Queries) RingGull(ctx context.Context, params RingGullParams) error
1func (queries *Queries) RingGull(ctx context.Context, params RingGullParams) error
2
2
3type RingGullParams struct {
3type RingGullParams struct {
4	Ring     string
4	Ring     string
5	Nickname *string
5	Nickname *string
6}
6}

RecordSightingidentical

PostgreSQL
CockroachDB
1func (queries *Queries) RecordSighting(ctx context.Context, params RecordSightingParams) error
1func (queries *Queries) RecordSighting(ctx context.Context, params RecordSightingParams) error
2
2
3type RecordSightingParams struct {
3type RecordSightingParams struct {
4	GullID int64
4	GullID int64
5	SeenOn time.Time
5	SeenOn time.Time
6	Place  string
6	Place  string
7}
7}

ListSightingsidentical

PostgreSQL
CockroachDB
1func (queries *Queries) ListSightings(ctx context.Context) ([]ListSightingsRow, error)
1func (queries *Queries) ListSightings(ctx context.Context) ([]ListSightingsRow, error)
2
2
3type ListSightingsRow struct {
3type ListSightingsRow struct {
4	Ring     string  `json:"ring"`
4	Ring     string  `json:"ring"`
5	Nickname *string `json:"nickname"`
5	Nickname *string `json:"nickname"`
6	Place    *string `json:"place"`
6	Place    *string `json:"place"`
7}
7}

SummariseGullsidentical

PostgreSQL
CockroachDB
1func (queries *Queries) SummariseGulls(ctx context.Context) ([]SummariseGullsRow, error)
1func (queries *Queries) SummariseGulls(ctx context.Context) ([]SummariseGullsRow, error)
2
2
3type SummariseGullsRow struct {
3type SummariseGullsRow struct {
4	Ring      string     `json:"ring"`
4	Ring      string     `json:"ring"`
5	KnownAs   string     `json:"known_as"`
5	KnownAs   string     `json:"known_as"`
6	TimesSeen int64      `json:"times_seen"`
6	TimesSeen int64      `json:"times_seen"`
7	LastSeen  *time.Time `json:"last_seen"`
7	LastSeen  *time.Time `json:"last_seen"`
8}
8}

The program

example.goidentical

PostgreSQL
CockroachDB
1package main
1package main
2
2
3import (
3import (
4	"context"
4	"context"
5	"database/sql"
5	"database/sql"
6	"fmt"
6	"fmt"
7	"io"
7	"io"
8	"time"
8	"time"
9
9
10	"seagull.sh/seagull/examples/postgres/004_nullability/internal/store"
10	"seagull.sh/seagull/examples/postgres/004_nullability/internal/store"
11)
11)
12
12
13// run rings three gulls, records where two of them were seen, and lists them twice.
13// run rings three gulls, records where two of them were seen, and lists them twice.
14// Seagull decides each field's nullability from the SQL, and a field that can be NULL is
14// Seagull decides each field's nullability from the SQL, and a field that can be NULL is
15// a pointer:
15// a pointer:
16//
16//
17//   - gulls.nickname has no NOT NULL, so Nickname is *string, as a parameter and a
17//   - gulls.nickname has no NOT NULL, so Nickname is *string, as a parameter and a
18//     column;
18//     column;
19//   - sightings.place is NOT NULL, but the LEFT JOIN gives a gull with no sightings a
19//   - sightings.place is NOT NULL, but the LEFT JOIN gives a gull with no sightings a
20//     NULL place, so Place is *string;
20//     NULL place, so Place is *string;
21//   - COALESCE with a non-NULL last argument is never NULL, so KnownAs is string;
21//   - COALESCE with a non-NULL last argument is never NULL, so KnownAs is string;
22//   - COUNT is never NULL, but MAX over no rows is, so TimesSeen is int64 and LastSeen is
22//   - COUNT is never NULL, but MAX over no rows is, so TimesSeen is int64 and LastSeen is
23//     *time.Time, keeping the DATE column's type.
23//     *time.Time, keeping the DATE column's type.
24//
24//
25// Takes database (*sql.DB) which is a migrated database.
25// Takes database (*sql.DB) which is a migrated database.
26// Takes output (io.Writer) which receives both listings.
26// Takes output (io.Writer) which receives both listings.
27//
27//
28// Returns error when a query fails.
28// Returns error when a query fails.
29func run(ctx context.Context, database *sql.DB, output io.Writer) error {
29func run(ctx context.Context, database *sql.DB, output io.Writer) error {
30	queries := store.New(database)
30	queries := store.New(database)
31
31
32	gulls := []store.RingGullParams{
32	gulls := []store.RingGullParams{
33		{Ring: "GB1001", Nickname: new("Steven")},
33		{Ring: "GB1001", Nickname: new("Steven")},
34		{Ring: "GB1002", Nickname: nil},
34		{Ring: "GB1002", Nickname: nil},
35		{Ring: "GB1003", Nickname: new("Gwen")},
35		{Ring: "GB1003", Nickname: new("Gwen")},
36	}
36	}
37	for _, gull := range gulls {
37	for _, gull := range gulls {
38		if err := queries.RingGull(ctx, gull); err != nil {
38		if err := queries.RingGull(ctx, gull); err != nil {
39			return fmt.Errorf("ringing %s: %w", gull.Ring, err)
39			return fmt.Errorf("ringing %s: %w", gull.Ring, err)
40		}
40		}
41	}
41	}
42	sightings := []store.RecordSightingParams{
42	sightings := []store.RecordSightingParams{
43		{GullID: 1, SeenOn: mayDay(1), Place: "the pier"},
43		{GullID: 1, SeenOn: mayDay(1), Place: "the pier"},
44		{GullID: 1, SeenOn: mayDay(3), Place: "the chip shop"},
44		{GullID: 1, SeenOn: mayDay(3), Place: "the chip shop"},
45		{GullID: 2, SeenOn: mayDay(2), Place: "the harbour wall"},
45		{GullID: 2, SeenOn: mayDay(2), Place: "the harbour wall"},
46	}
46	}
47	for _, sighting := range sightings {
47	for _, sighting := range sightings {
48		if err := queries.RecordSighting(ctx, sighting); err != nil {
48		if err := queries.RecordSighting(ctx, sighting); err != nil {
49			return fmt.Errorf("recording a sighting: %w", err)
49			return fmt.Errorf("recording a sighting: %w", err)
50		}
50		}
51	}
51	}
52
52
53	rows, err := queries.ListSightings(ctx)
53	rows, err := queries.ListSightings(ctx)
54	if err != nil {
54	if err != nil {
55		return fmt.Errorf("listing sightings: %w", err)
55		return fmt.Errorf("listing sightings: %w", err)
56	}
56	}
57	fmt.Fprintln(output, "ring    nickname  place")
57	fmt.Fprintln(output, "ring    nickname  place")
58	for _, row := range rows {
58	for _, row := range rows {
59		fmt.Fprintf(output, "%-7s %-9s %s\n", row.Ring, orNull(row.Nickname), orNull(row.Place))
59		fmt.Fprintf(output, "%-7s %-9s %s\n", row.Ring, orNull(row.Nickname), orNull(row.Place))
60	}
60	}
61
61
62	summaries, err := queries.SummariseGulls(ctx)
62	summaries, err := queries.SummariseGulls(ctx)
63	if err != nil {
63	if err != nil {
64		return fmt.Errorf("summarising gulls: %w", err)
64		return fmt.Errorf("summarising gulls: %w", err)
65	}
65	}
66	fmt.Fprintln(output, "\nring    known as     times seen  last seen")
66	fmt.Fprintln(output, "\nring    known as     times seen  last seen")
67	for _, summary := range summaries {
67	for _, summary := range summaries {
68		lastSeen := "NULL"
68		lastSeen := "NULL"
69		if summary.LastSeen != nil {
69		if summary.LastSeen != nil {
70			lastSeen = summary.LastSeen.Format(time.DateOnly)
70			lastSeen = summary.LastSeen.Format(time.DateOnly)
71		}
71		}
72		fmt.Fprintf(output, "%-7s %-12s %-11d %s\n", summary.Ring, summary.KnownAs, summary.TimesSeen, lastSeen)
72		fmt.Fprintf(output, "%-7s %-12s %-11d %s\n", summary.Ring, summary.KnownAs, summary.TimesSeen, lastSeen)
73	}
73	}
74	return nil
74	return nil
75}
75}
76
76
77// mayDay returns a day in May 2026, for the DATE column.
77// mayDay returns a day in May 2026, for the DATE column.
78//
78//
79// Takes day (int) which is the day of the month.
79// Takes day (int) which is the day of the month.
80//
80//
81// Returns time.Time which is midnight UTC on that day.
81// Returns time.Time which is midnight UTC on that day.
82func mayDay(day int) time.Time {
82func mayDay(day int) time.Time {
83	return time.Date(2026, time.May, day, 0, 0, 0, 0, time.UTC)
83	return time.Date(2026, time.May, day, 0, 0, 0, 0, time.UTC)
84}
84}
85
85
86// orNull returns the text a nullable field holds, or NULL when the field is nil.
86// orNull returns the text a nullable field holds, or NULL when the field is nil.
87//
87//
88// Takes value (*string) which is a nullable field.
88// Takes value (*string) which is a nullable field.
89//
89//
90// Returns string which is the text to print.
90// Returns string which is the text to print.
91func orNull(value *string) string {
91func orNull(value *string) string {
92	if value == nil {
92	if value == nil {
93		return "NULL"
93		return "NULL"
94	}
94	}
95	return *value
95	return *value
96}
96}