Compare · 004

Nullability through joins, COALESCE and aggregates: DuckDB and TimescaleDB

Every file of the DuckDB and TimescaleDB versions side by side, with the lines that differ marked.

Compare DuckDB withSQLitePostgreSQLCockroachDBTimescaleDBMySQLMariaDBClickHouse

Configuration

sgl.yamldiffers
DuckDB
TimescaleDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: colony
4 - name: colony
5 dialect: duckdb
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

Migrations

db/migrations/0001_gulls.up.sqldiffers
DuckDB
TimescaleDB
1CREATE SEQUENCE gull_ids;
2
3CREATE SEQUENCE sighting_ids;
4
5CREATE TABLE gulls (
1CREATE TABLE gulls (
6 id BIGINT PRIMARY KEY DEFAULT nextval('gull_ids'),
2 id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
7 ring VARCHAR NOT NULL UNIQUE,
3 ring TEXT NOT NULL UNIQUE,
8 nickname VARCHAR
4 nickname TEXT
9);
5);
10
6
11CREATE TABLE sightings (
7CREATE TABLE sightings (
12 id BIGINT PRIMARY KEY DEFAULT nextval('sighting_ids'),
8 id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
13 gull_id BIGINT NOT NULL REFERENCES gulls (id),
9 gull_id BIGINT NOT NULL REFERENCES gulls (id),
14 seen_on DATE NOT NULL,
10 seen_on DATE NOT NULL,
15 place VARCHAR NOT NULL
11 place TEXT NOT NULL
16);
12);
db/migrations/0001_gulls.down.sqldiffers
DuckDB
TimescaleDB
1DROP TABLE sightings;
1DROP TABLE sightings;
2DROP TABLE gulls;
2DROP TABLE gulls;
3DROP SEQUENCE sighting_ids;
4DROP SEQUENCE gull_ids;

Queries

RingGullidentical
DuckDB · db/queries/gulls.sql
TimescaleDB · 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
DuckDB · db/queries/gulls.sql
TimescaleDB · 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
DuckDB · db/queries/gulls.sql
TimescaleDB · 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;
SummariseGullsdiffers
DuckDB · db/queries/gulls.sql
TimescaleDB · 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, gulls.ring, gulls.nickname
9GROUP BY gulls.id
10ORDER BY gulls.id;
10ORDER BY gulls.id;

What sgl generates

RingGullidentical
DuckDB
TimescaleDB
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
DuckDB
TimescaleDB
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
DuckDB
TimescaleDB
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
DuckDB
TimescaleDB
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.godiffers
DuckDB
TimescaleDB
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/duckdb/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, as seen_on is a DATE.
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: day(1), Place: "the pier"},
43 {GullID: 1, SeenOn: mayDay(1), Place: "the pier"},
44 {GullID: 1, SeenOn: day(3), Place: "the chip shop"},
44 {GullID: 1, SeenOn: mayDay(3), Place: "the chip shop"},
45 {GullID: 2, SeenOn: day(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// day returns a date in May 2026, the month the sightings were made.
77// mayDay returns a day in May 2026, for the DATE column.
78//
78//
79// Takes dayOfMonth (int) which is the day.
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 day(dayOfMonth int) time.Time {
82func mayDay(day int) time.Time {
83 return time.Date(2026, time.May, dayOfMonth, 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}