Compare · 019

Types through CTEs, window functions and views: PostgreSQL and TimescaleDB

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

Compare PostgreSQL withSQLiteCockroachDBTimescaleDBMySQLMariaDBDuckDBClickHouse

Configuration

sgl.yamlidentical
PostgreSQL
TimescaleDB
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
12 migrate:
12 migrate:
13 dsn: ${env:DATABASE_URL}
13 dsn: ${env:DATABASE_URL}

Migrations

db/migrations/0001_gulls.up.sqlidentical
PostgreSQL
TimescaleDB
1CREATE TABLE gulls (
1CREATE TABLE gulls (
2 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
2 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3 name TEXT NOT NULL,
3 name TEXT NOT NULL,
4 parent_id BIGINT REFERENCES gulls (id),
4 parent_id BIGINT REFERENCES gulls (id),
5 beach TEXT NOT NULL
5 beach TEXT NOT NULL
6);
6);
7
7
8CREATE TABLE thefts (
8CREATE TABLE thefts (
9 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
9 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
10 gull_id BIGINT NOT NULL REFERENCES gulls (id),
10 gull_id BIGINT NOT NULL REFERENCES gulls (id),
11 snack TEXT NOT NULL,
11 snack TEXT NOT NULL,
12 stolen_on DATE NOT NULL
12 stolen_on DATE NOT NULL
13);
13);
14
14
15CREATE VIEW beach_records AS
15CREATE VIEW beach_records AS
16SELECT
16SELECT
17 gulls.beach,
17 gulls.beach,
18 COUNT(DISTINCT gulls.id) AS gulls,
18 COUNT(DISTINCT gulls.id) AS gulls,
19 COUNT(thefts.id) AS thefts,
19 COUNT(thefts.id) AS thefts,
20 MAX(thefts.stolen_on) AS last_theft
20 MAX(thefts.stolen_on) AS last_theft
21FROM gulls
21FROM gulls
22LEFT JOIN thefts ON thefts.gull_id = gulls.id
22LEFT JOIN thefts ON thefts.gull_id = gulls.id
23GROUP BY gulls.beach;
23GROUP BY gulls.beach;
db/migrations/0001_gulls.down.sqlidentical
PostgreSQL
TimescaleDB
1DROP VIEW beach_records;
1DROP VIEW beach_records;
2DROP TABLE thefts;
2DROP TABLE thefts;
3DROP TABLE gulls;
3DROP TABLE gulls;

Queries

HatchGullidentical
PostgreSQL · db/queries/gulls.sql
TimescaleDB · db/queries/gulls.sql
1-- sgl.query(HatchGull, exec)
1-- sgl.query(HatchGull, exec)
2INSERT INTO gulls (name, parent_id, beach) VALUES ($1, $2, $3);
2INSERT INTO gulls (name, parent_id, beach) VALUES ($1, $2, $3);
RecordTheftidentical
PostgreSQL · db/queries/gulls.sql
TimescaleDB · db/queries/gulls.sql
1-- sgl.query(RecordTheft, exec)
1-- sgl.query(RecordTheft, exec)
2INSERT INTO thefts (gull_id, snack, stolen_on) VALUES ($1, $2, $3);
2INSERT INTO thefts (gull_id, snack, stolen_on) VALUES ($1, $2, $3);
FamilyTreeidentical
PostgreSQL · db/queries/gulls.sql
TimescaleDB · db/queries/gulls.sql
1-- sgl.query(FamilyTree, many)
1-- sgl.query(FamilyTree, many)
2WITH RECURSIVE descendants AS (
2WITH RECURSIVE descendants AS (
3 SELECT id, name, parent_id, 0::BIGINT AS generation
3 SELECT id, name, parent_id, 0::BIGINT AS generation
4 FROM gulls
4 FROM gulls
5 WHERE id = $1
5 WHERE id = $1
6 UNION ALL
6 UNION ALL
7 SELECT gulls.id, gulls.name, gulls.parent_id, descendants.generation + 1
7 SELECT gulls.id, gulls.name, gulls.parent_id, descendants.generation + 1
8 FROM gulls
8 FROM gulls
9 JOIN descendants ON gulls.parent_id = descendants.id
9 JOIN descendants ON gulls.parent_id = descendants.id
10)
10)
11SELECT descendants.name, descendants.generation, parents.name AS parent
11SELECT descendants.name, descendants.generation, parents.name AS parent
12FROM descendants
12FROM descendants
13LEFT JOIN gulls AS parents ON parents.id = descendants.parent_id
13LEFT JOIN gulls AS parents ON parents.id = descendants.parent_id
14ORDER BY descendants.generation, descendants.id;
14ORDER BY descendants.generation, descendants.id;
TheftLeagueidentical
PostgreSQL · db/queries/gulls.sql
TimescaleDB · db/queries/gulls.sql
1-- sgl.query(TheftLeague, many)
1-- sgl.query(TheftLeague, many)
2WITH totals AS (
2WITH totals AS (
3 SELECT gulls.name, gulls.beach, COUNT(thefts.id) AS thefts
3 SELECT gulls.name, gulls.beach, COUNT(thefts.id) AS thefts
4 FROM gulls
4 FROM gulls
5 LEFT JOIN thefts ON thefts.gull_id = gulls.id
5 LEFT JOIN thefts ON thefts.gull_id = gulls.id
6 GROUP BY gulls.id
6 GROUP BY gulls.id
7)
7)
8SELECT
8SELECT
9 name,
9 name,
10 beach,
10 beach,
11 thefts,
11 thefts,
12 RANK() OVER (PARTITION BY beach ORDER BY thefts DESC) AS beach_rank,
12 RANK() OVER (PARTITION BY beach ORDER BY thefts DESC) AS beach_rank,
13 SUM(thefts) OVER (PARTITION BY beach) AS beach_thefts
13 SUM(thefts) OVER (PARTITION BY beach) AS beach_thefts
14FROM totals
14FROM totals
15ORDER BY beach, beach_rank, name;
15ORDER BY beach, beach_rank, name;
BeachRecordsidentical
PostgreSQL · db/queries/gulls.sql
TimescaleDB · db/queries/gulls.sql
1-- sgl.query(BeachRecords, many)
1-- sgl.query(BeachRecords, many)
2SELECT * FROM beach_records ORDER BY beach;
2SELECT * FROM beach_records ORDER BY beach;

What sgl generates

HatchGullidentical
PostgreSQL
TimescaleDB
1func (queries *Queries) HatchGull(ctx context.Context, params HatchGullParams) error
1func (queries *Queries) HatchGull(ctx context.Context, params HatchGullParams) error
2
2
3type HatchGullParams struct {
3type HatchGullParams struct {
4 Name string
4 Name string
5 ParentID *int64
5 ParentID *int64
6 Beach string
6 Beach string
7}
7}
RecordTheftidentical
PostgreSQL
TimescaleDB
1func (queries *Queries) RecordTheft(ctx context.Context, params RecordTheftParams) error
1func (queries *Queries) RecordTheft(ctx context.Context, params RecordTheftParams) error
2
2
3type RecordTheftParams struct {
3type RecordTheftParams struct {
4 GullID int64
4 GullID int64
5 Snack string
5 Snack string
6 StolenOn time.Time
6 StolenOn time.Time
7}
7}
FamilyTreeidentical
PostgreSQL
TimescaleDB
1func (queries *Queries) FamilyTree(ctx context.Context, id int64) ([]FamilyTreeRow, error)
1func (queries *Queries) FamilyTree(ctx context.Context, id int64) ([]FamilyTreeRow, error)
2
2
3type FamilyTreeRow struct {
3type FamilyTreeRow struct {
4 Name string `json:"name"`
4 Name string `json:"name"`
5 Generation int64 `json:"generation"`
5 Generation int64 `json:"generation"`
6 Parent *string `json:"parent"`
6 Parent *string `json:"parent"`
7}
7}
TheftLeagueidentical
PostgreSQL
TimescaleDB
1func (queries *Queries) TheftLeague(ctx context.Context) ([]TheftLeagueRow, error)
1func (queries *Queries) TheftLeague(ctx context.Context) ([]TheftLeagueRow, error)
2
2
3type TheftLeagueRow struct {
3type TheftLeagueRow struct {
4 Name string `json:"name"`
4 Name string `json:"name"`
5 Beach string `json:"beach"`
5 Beach string `json:"beach"`
6 Thefts int64 `json:"thefts"`
6 Thefts int64 `json:"thefts"`
7 BeachRank int64 `json:"beach_rank"`
7 BeachRank int64 `json:"beach_rank"`
8 BeachThefts *apd.Decimal `json:"beach_thefts"`
8 BeachThefts *apd.Decimal `json:"beach_thefts"`
9}
9}
BeachRecordsidentical
PostgreSQL
TimescaleDB
1func (queries *Queries) BeachRecords(ctx context.Context) ([]BeachRecordsRow, error)
1func (queries *Queries) BeachRecords(ctx context.Context) ([]BeachRecordsRow, error)
2
2
3type BeachRecordsRow struct {
3type BeachRecordsRow struct {
4 Beach string `json:"beach"`
4 Beach string `json:"beach"`
5 Gulls int64 `json:"gulls"`
5 Gulls int64 `json:"gulls"`
6 Thefts int64 `json:"thefts"`
6 Thefts int64 `json:"thefts"`
7 LastTheft *time.Time `json:"last_theft"`
7 LastTheft *time.Time `json:"last_theft"`
8}
8}

The program

example.goidentical
PostgreSQL
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/postgres/019_ctes_and_windows/internal/store"
10 "seagull.sh/seagull/examples/postgres/019_ctes_and_windows/internal/store"
11)
11)
12
12
13// run hatches a colony of gulls, records their snack thefts and reads them back three
13// run hatches a colony of gulls, records their snack thefts and reads them back three
14// ways. Seagull follows each column's type and nullability through the SQL:
14// ways. Seagull follows each column's type and nullability through the SQL:
15//
15//
16// - FamilyTree's recursive CTE starts from one gull and joins its chicks generation by
16// - FamilyTree's recursive CTE starts from one gull and joins its chicks generation by
17// generation; generation is an int64 from 0::BIGINT AS generation and generation + 1,
17// generation; generation is an int64 from 0::BIGINT AS generation and generation + 1,
18// and parent is *string because the LEFT JOIN finds no parent for the first gull;
18// and parent is *string because the LEFT JOIN finds no parent for the first gull;
19// - TheftLeague counts thefts in a CTE, then ranks gulls on each beach with RANK() and
19// - TheftLeague counts thefts in a CTE, then ranks gulls on each beach with RANK() and
20// totals the beach with SUM() OVER; COUNT and RANK are bigint, so int64, while SUM of
20// totals the beach with SUM() OVER; COUNT and RANK are bigint, so int64, while SUM of
21// a bigint is numeric in PostgreSQL, so BeachThefts is a nullable *apd.Decimal;
21// a bigint is numeric in PostgreSQL, so BeachThefts is a nullable *apd.Decimal;
22// - BeachRecords reads the beach_records view from the migration as if it were a table,
22// - BeachRecords reads the beach_records view from the migration as if it were a table,
23// and its columns keep the types the view's own SELECT gives them: MAX over a DATE
23// and its columns keep the types the view's own SELECT gives them: MAX over a DATE
24// column is a *time.Time, nil for a beach with no thefts. The query is written as
24// column is a *time.Time, nil for a beach with no thefts. The query is written as
25// SELECT *, and the generated SQL names the view's four columns instead, so a
25// SELECT *, and the generated SQL names the view's four columns instead, so a
26// migration that later adds a column to the view does not break the code already
26// migration that later adds a column to the view does not break the code already
27// deployed, which scans exactly those four. go.keep_stars: true in sgl.yaml keeps the
27// deployed, which scans exactly those four. go.keep_stars: true in sgl.yaml keeps the
28// star as written.
28// star as written.
29//
29//
30// Takes database (*sql.DB) which is a migrated database.
30// Takes database (*sql.DB) which is a migrated database.
31// Takes output (io.Writer) which receives the three listings.
31// Takes output (io.Writer) which receives the three listings.
32//
32//
33// Returns error when a query fails.
33// Returns error when a query fails.
34func run(ctx context.Context, database *sql.DB, output io.Writer) error {
34func run(ctx context.Context, database *sql.DB, output io.Writer) error {
35 queries := store.New(database)
35 queries := store.New(database)
36
36
37 gulls := []store.HatchGullParams{
37 gulls := []store.HatchGullParams{
38 {Name: "Steven", ParentID: nil, Beach: "Porthmeor"},
38 {Name: "Steven", ParentID: nil, Beach: "Porthmeor"},
39 {Name: "Gwen", ParentID: new(int64(1)), Beach: "Porthmeor"},
39 {Name: "Gwen", ParentID: new(int64(1)), Beach: "Porthmeor"},
40 {Name: "Jago", ParentID: new(int64(1)), Beach: "Porthminster"},
40 {Name: "Jago", ParentID: new(int64(1)), Beach: "Porthminster"},
41 {Name: "Kerensa", ParentID: new(int64(2)), Beach: "Porthmeor"},
41 {Name: "Kerensa", ParentID: new(int64(2)), Beach: "Porthmeor"},
42 {Name: "Tristan", ParentID: nil, Beach: "Porthminster"},
42 {Name: "Tristan", ParentID: nil, Beach: "Porthminster"},
43 {Name: "Morwenna", ParentID: nil, Beach: "Gwithian"},
43 {Name: "Morwenna", ParentID: nil, Beach: "Gwithian"},
44 }
44 }
45 for _, gull := range gulls {
45 for _, gull := range gulls {
46 if err := queries.HatchGull(ctx, gull); err != nil {
46 if err := queries.HatchGull(ctx, gull); err != nil {
47 return fmt.Errorf("hatching %s: %w", gull.Name, err)
47 return fmt.Errorf("hatching %s: %w", gull.Name, err)
48 }
48 }
49 }
49 }
50 thefts := []store.RecordTheftParams{
50 thefts := []store.RecordTheftParams{
51 {GullID: 1, Snack: "chips", StolenOn: july(1)},
51 {GullID: 1, Snack: "chips", StolenOn: july(1)},
52 {GullID: 1, Snack: "pasty", StolenOn: july(3)},
52 {GullID: 1, Snack: "pasty", StolenOn: july(3)},
53 {GullID: 1, Snack: "ice cream", StolenOn: july(4)},
53 {GullID: 1, Snack: "ice cream", StolenOn: july(4)},
54 {GullID: 2, Snack: "chips", StolenOn: july(2)},
54 {GullID: 2, Snack: "chips", StolenOn: july(2)},
55 {GullID: 3, Snack: "pasty", StolenOn: july(2)},
55 {GullID: 3, Snack: "pasty", StolenOn: july(2)},
56 {GullID: 3, Snack: "chips", StolenOn: july(5)},
56 {GullID: 3, Snack: "chips", StolenOn: july(5)},
57 {GullID: 5, Snack: "sandwich", StolenOn: july(3)},
57 {GullID: 5, Snack: "sandwich", StolenOn: july(3)},
58 }
58 }
59 for _, theft := range thefts {
59 for _, theft := range thefts {
60 if err := queries.RecordTheft(ctx, theft); err != nil {
60 if err := queries.RecordTheft(ctx, theft); err != nil {
61 return fmt.Errorf("recording a theft: %w", err)
61 return fmt.Errorf("recording a theft: %w", err)
62 }
62 }
63 }
63 }
64
64
65 family, err := queries.FamilyTree(ctx, 1)
65 family, err := queries.FamilyTree(ctx, 1)
66 if err != nil {
66 if err != nil {
67 return fmt.Errorf("tracing Steven's family: %w", err)
67 return fmt.Errorf("tracing Steven's family: %w", err)
68 }
68 }
69 fmt.Fprintln(output, "Steven's family:")
69 fmt.Fprintln(output, "Steven's family:")
70 for _, member := range family {
70 for _, member := range family {
71 fmt.Fprintf(output, " generation %d %-8s parent %s\n", member.Generation, member.Name, orNull(member.Parent))
71 fmt.Fprintf(output, " generation %d %-8s parent %s\n", member.Generation, member.Name, orNull(member.Parent))
72 }
72 }
73
73
74 league, err := queries.TheftLeague(ctx)
74 league, err := queries.TheftLeague(ctx)
75 if err != nil {
75 if err != nil {
76 return fmt.Errorf("ranking thieves: %w", err)
76 return fmt.Errorf("ranking thieves: %w", err)
77 }
77 }
78 fmt.Fprintln(output, "theft league:")
78 fmt.Fprintln(output, "theft league:")
79 for _, entry := range league {
79 for _, entry := range league {
80 beachThefts := "NULL"
80 beachThefts := "NULL"
81 if entry.BeachThefts != nil {
81 if entry.BeachThefts != nil {
82 beachThefts = entry.BeachThefts.String()
82 beachThefts = entry.BeachThefts.String()
83 }
83 }
84 fmt.Fprintf(output, " %-13s #%d %-8s %d of %s\n", entry.Beach, entry.BeachRank, entry.Name, entry.Thefts, beachThefts)
84 fmt.Fprintf(output, " %-13s #%d %-8s %d of %s\n", entry.Beach, entry.BeachRank, entry.Name, entry.Thefts, beachThefts)
85 }
85 }
86
86
87 records, err := queries.BeachRecords(ctx)
87 records, err := queries.BeachRecords(ctx)
88 if err != nil {
88 if err != nil {
89 return fmt.Errorf("reading beach records: %w", err)
89 return fmt.Errorf("reading beach records: %w", err)
90 }
90 }
91 fmt.Fprintln(output, "beach records:")
91 fmt.Fprintln(output, "beach records:")
92 for _, record := range records {
92 for _, record := range records {
93 lastTheft := "NULL"
93 lastTheft := "NULL"
94 if record.LastTheft != nil {
94 if record.LastTheft != nil {
95 lastTheft = record.LastTheft.Format(time.DateOnly)
95 lastTheft = record.LastTheft.Format(time.DateOnly)
96 }
96 }
97 fmt.Fprintf(output, " %-13s gulls %d, thefts %d, last theft %s\n", record.Beach, record.Gulls, record.Thefts, lastTheft)
97 fmt.Fprintf(output, " %-13s gulls %d, thefts %d, last theft %s\n", record.Beach, record.Gulls, record.Thefts, lastTheft)
98 }
98 }
99 return nil
99 return nil
100}
100}
101
101
102// july returns a day in July 2026, the month the thefts happen in.
102// july returns a day in July 2026, the month the thefts happen in.
103//
103//
104// Takes day (int) which is the day of the month.
104// Takes day (int) which is the day of the month.
105//
105//
106// Returns time.Time which is midnight UTC on that day, as a DATE column holds it.
106// Returns time.Time which is midnight UTC on that day, as a DATE column holds it.
107func july(day int) time.Time {
107func july(day int) time.Time {
108 return time.Date(2026, time.July, day, 0, 0, 0, 0, time.UTC)
108 return time.Date(2026, time.July, day, 0, 0, 0, 0, time.UTC)
109}
109}
110
110
111// orNull formats a nullable field, or returns NULL when the field is nil.
111// orNull formats a nullable field, or returns NULL when the field is nil.
112//
112//
113// Takes value (*T) which is a nullable field.
113// Takes value (*T) which is a nullable field.
114//
114//
115// Returns string which is the text to print.
115// Returns string which is the text to print.
116func orNull[T any](value *T) string {
116func orNull[T any](value *T) string {
117 if value == nil {
117 if value == nil {
118 return "NULL"
118 return "NULL"
119 }
119 }
120 return fmt.Sprint(*value)
120 return fmt.Sprint(*value)
121}
121}