Compare · 005

From SQL types to Go types: TimescaleDB and DuckDB

Every file of the TimescaleDB and DuckDB versions side by side, with the lines that differ marked. 2 parameter or column types differ in Go.

Compare TimescaleDB withSQLitePostgreSQLMySQLMariaDBDuckDBClickHouse

Configuration

sgl.yamldiffers
TimescaleDB
DuckDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: landings
4 - name: harbour
5 dialect: postgres
5 dialect: duckdb
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_landings.up.sqldiffers
TimescaleDB
DuckDB
1CREATE SEQUENCE landing_ids;
2
1CREATE TABLE landings (
3CREATE TABLE landings (
2 id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
4 id BIGINT PRIMARY KEY DEFAULT nextval('landing_ids'),
3 catch_id UUID NOT NULL,
5 species VARCHAR NOT NULL,
4 species TEXT NOT NULL,
6 quay UTINYINT NOT NULL,
5 boxes SMALLINT NOT NULL,
7 boxes SMALLINT NOT NULL,
6 fish INTEGER NOT NULL,
8 fish INTEGER NOT NULL,
7 weight_g BIGINT NOT NULL,
9 weight_g BIGINT NOT NULL,
8 length_cm REAL NOT NULL,
10 length_cm DOUBLE NOT NULL,
9 sea_temp DOUBLE PRECISION NOT NULL,
11 water_temp REAL NOT NULL,
10 price NUMERIC(8, 2) NOT NULL,
12 on_ice BOOLEAN NOT NULL,
11 on_ice BOOLEAN NOT NULL,
13 landed_at TIMESTAMP NOT NULL,
12 landed_on DATE NOT NULL,
14 landed_on DATE NOT NULL,
13 landed_at TIMESTAMPTZ NOT NULL,
15 catch_id UUID NOT NULL,
14 harbour_ip INET NOT NULL,
16 auction_lot HUGEINT NOT NULL,
15 tag BYTEA NOT NULL,
17 tag BLOB NOT NULL,
16 notes TEXT
18 notes VARCHAR
17);
19);
db/migrations/0001_landings.down.sqldiffers
TimescaleDB
DuckDB
1DROP TABLE landings;
1DROP TABLE landings;
2DROP SEQUENCE landing_ids;

Queries

RecordLandingdiffers
TimescaleDB · db/queries/landings.sql
DuckDB · db/queries/landings.sql
1-- sgl.query(RecordLanding, one)
1-- sgl.query(RecordLanding, one)
2INSERT INTO landings (catch_id, species, boxes, fish, weight_g, length_cm, sea_temp, price, on_ice, landed_on, landed_at, harbour_ip, tag, notes)
2INSERT INTO landings (species, quay, boxes, fish, weight_g, length_cm, water_temp, on_ice, landed_at, landed_on, catch_id, auction_lot, tag, notes)
3VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
3VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
4RETURNING id;
4RETURNING id;
GetLandingdiffers
TimescaleDB · db/queries/landings.sql
DuckDB · db/queries/landings.sql
1-- sgl.query(GetLanding, one)
1-- sgl.query(GetLanding, one)
2SELECT id, catch_id, species, boxes, fish, weight_g, length_cm, sea_temp, price, on_ice, landed_on, landed_at, harbour_ip, tag, notes
2SELECT id, species, quay, boxes, fish, weight_g, length_cm, water_temp, on_ice, landed_at, landed_on, catch_id, auction_lot, tag, notes
3FROM landings
3FROM landings
4WHERE id = $1;
4WHERE id = $1;

What sgl generates

RecordLandingdiffers
TimescaleDB
DuckDB
1func (queries *Queries) RecordLanding(ctx context.Context, params RecordLandingParams) (RecordLandingRow, error)
1func (queries *Queries) RecordLanding(ctx context.Context, params RecordLandingParams) (RecordLandingRow, error)
2
2
3type RecordLandingParams struct {
3type RecordLandingParams struct {
4 CatchID uuid.UUID
4 Species string
5 Species string
5 Quay uint8
6 Boxes int16
6 Boxes int16
7 Fish int32
7 Fish int32
8 WeightG int64
8 WeightG int64
9 LengthCm float32
9 LengthCm float64
10 SeaTemp float64
10 WaterTemp float32
11 Price apd.Decimal
11 OnIce bool
12 OnIce bool
12 LandedAt time.Time
13 LandedOn time.Time
13 LandedOn time.Time
14 LandedAt time.Time
14 CatchID uuid.UUID
15 HarbourIP any
15 AuctionLot *big.Int
16 Tag []byte
16 Tag []byte
17 Notes *string
17 Notes *string
18}
18}
19
19
20type RecordLandingRow struct {
20type RecordLandingRow struct {
21 ID int64 `json:"id"`
21 ID int64 `json:"id"`
22}
22}
GetLandingdiffers
TimescaleDB
DuckDB
1func (queries *Queries) GetLanding(ctx context.Context, id int64) (GetLandingRow, error)
1func (queries *Queries) GetLanding(ctx context.Context, id int64) (GetLandingRow, error)
2
2
3type GetLandingRow struct {
3type GetLandingRow struct {
4 ID int64 `json:"id"`
4 ID int64 `json:"id"`
5 CatchID uuid.UUID `json:"catch_id"`
5 Species string `json:"species"`
6 Species string `json:"species"`
6 Quay uint8 `json:"quay"`
7 Boxes int16 `json:"boxes"`
7 Boxes int16 `json:"boxes"`
8 Fish int32 `json:"fish"`
8 Fish int32 `json:"fish"`
9 WeightG int64 `json:"weight_g"`
9 WeightG int64 `json:"weight_g"`
10 LengthCm float32 `json:"length_cm"`
10 LengthCm float64 `json:"length_cm"`
11 SeaTemp float64 `json:"sea_temp"`
11 WaterTemp float32 `json:"water_temp"`
12 Price apd.Decimal `json:"price"`
12 OnIce bool `json:"on_ice"`
13 OnIce bool `json:"on_ice"`
13 LandedAt time.Time `json:"landed_at"`
14 LandedOn time.Time `json:"landed_on"`
14 LandedOn time.Time `json:"landed_on"`
15 LandedAt time.Time `json:"landed_at"`
15 CatchID uuid.UUID `json:"catch_id"`
16 HarbourIP any `json:"harbour_ip"`
16 AuctionLot *big.Int `json:"auction_lot"`
17 Tag []byte `json:"tag"`
17 Tag []byte `json:"tag"`
18 Notes *string `json:"notes"`
18 Notes *string `json:"notes"`
19}
19}

The program

example.godiffers
TimescaleDB
DuckDB
1package main
1package main
2
2
3import (
3import (
4 "context"
4 "context"
5 "database/sql"
5 "database/sql"
6 "errors"
6 "fmt"
7 "fmt"
7 "io"
8 "io"
9 "math/big"
8 "time"
10 "time"
9
11
10 "github.com/cockroachdb/apd/v3"
11 "github.com/google/uuid"
12 "github.com/google/uuid"
12
13
13 "seagull.sh/seagull/examples/postgres/005_type_mapping/internal/store"
14 "seagull.sh/seagull/examples/duckdb/005_type_mapping/internal/store"
14)
15)
15
16
16// run records a landing of fish and reads it back, printing the Go type sgl chose for
17// run records a landing of fish and reads it back, printing the Go type sgl chose for
17// each column of db/migrations. PostgreSQL's types are exact, so each maps by name:
18// each column. DuckDB declares every column's type exactly, and Seagull maps each one:
18//
19//
19// - SMALLINT, INTEGER and BIGINT give int16, int32 and int64;
20// - SMALLINT, INTEGER and BIGINT give int16, int32 and int64, and the unsigned UTINYINT
20// - REAL is single precision and gives float32, while DOUBLE PRECISION gives float64;
21// gives uint8;
21// - NUMERIC gives apd.Decimal from github.com/cockroachdb/apd/v3, which keeps money
22// - DOUBLE gives float64, but REAL is DuckDB's 4-byte float, so it gives float32;
22// exact, and UUID gives uuid.UUID from github.com/google/uuid;
23// - TIMESTAMP and DATE give time.Time, and UUID gives uuid.UUID from
23// - DATE and TIMESTAMPTZ give time.Time, and BYTEA gives []byte;
24// github.com/google/uuid;
24// - a type Seagull has no mapping for, such as INET, gives any, and the driver decides
25// - HUGEINT is a 128-bit integer with no Go equivalent, so it gives *big.Int, the type
25// what it holds: pgx's database/sql driver hands back the address as a string. Give
26// duckdb-go returns, even when the column is NOT NULL;
26// such a column a go_type override to pin it down.
27// - BLOB gives []byte, and a nullable VARCHAR gives *string.
27//
28//
28// Takes database (*sql.DB) which is a migrated database.
29// Takes database (*sql.DB) which is a migrated database.
29// Takes output (io.Writer) which receives one line per column.
30// Takes output (io.Writer) which receives one line per column.
30//
31//
31// Returns error when a query fails.
32// Returns error when a query fails.
32func run(ctx context.Context, database *sql.DB, output io.Writer) error {
33func run(ctx context.Context, database *sql.DB, output io.Writer) error {
33 queries := store.New(database)
34 queries := store.New(database)
34
35
36 // The auction lot number has 20 digits, too many for int64 or uint64.
37 lot, ok := new(big.Int).SetString("20260501000000000001", 10)
38 if !ok {
39 return errors.New("parsing the auction lot number")
40 }
35 recorded, err := queries.RecordLanding(ctx, store.RecordLandingParams{
41 recorded, err := queries.RecordLanding(ctx, store.RecordLandingParams{
36 CatchID: uuid.MustParse("6f1c0b8e-4b2a-4c55-9d7e-2a3f5e8c1d40"),
42 Species: "mackerel",
37 Species: "mackerel",
43 Quay: 3,
38 Boxes: 12,
44 Boxes: 12,
39 Fish: 480,
45 Fish: 480,
40 WeightG: 163_200,
46 WeightG: 163_200,
41 LengthCm: 31.5,
47 LengthCm: 31.5,
42 SeaTemp: 12.25,
48 WaterTemp: 11.25,
43 Price: *apd.New(41_280, -2),
49 OnIce: true,
44 OnIce: true,
50 LandedAt: time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),
45 LandedOn: time.Date(2026, time.May, 1, 0, 0, 0, 0, time.UTC),
51 LandedOn: time.Date(2026, time.May, 1, 0, 0, 0, 0, time.UTC),
46 LandedAt: time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),
52 CatchID: uuid.MustParse("6f1c2a5e-4d3b-4c2a-9e8f-1a2b3c4d5e6f"),
47 HarbourIP: "192.0.2.17",
53 AuctionLot: lot,
48 Tag: []byte{0x04, 0xa2, 0x19, 0xf3},
54 Tag: []byte{0x04, 0xa2, 0x19, 0xf3},
49 Notes: new("landed at Newlyn"),
55 Notes: new("landed at Newlyn"),
50 })
56 })
51 if err != nil {
57 if err != nil {
52 return fmt.Errorf("recording a landing: %w", err)
58 return fmt.Errorf("recording a landing: %w", err)
53 }
59 }
54
60
55 landing, err := queries.GetLanding(ctx, recorded.ID)
61 landing, err := queries.GetLanding(ctx, recorded.ID)
56 if err != nil {
62 if err != nil {
57 return fmt.Errorf("getting landing %d: %w", recorded.ID, err)
63 return fmt.Errorf("getting landing %d: %w", recorded.ID, err)
58 }
64 }
59 fmt.Fprintf(output, "id %-12T %d\n", landing.ID, landing.ID)
65 fmt.Fprintf(output, "id %-10T %d\n", landing.ID, landing.ID)
60 fmt.Fprintf(output, "catch_id %-12T %s\n", landing.CatchID, landing.CatchID)
66 fmt.Fprintf(output, "species %-10T %s\n", landing.Species, landing.Species)
61 fmt.Fprintf(output, "species %-12T %s\n", landing.Species, landing.Species)
67 fmt.Fprintf(output, "quay %-10T %d\n", landing.Quay, landing.Quay)
62 fmt.Fprintf(output, "boxes %-12T %d\n", landing.Boxes, landing.Boxes)
68 fmt.Fprintf(output, "boxes %-10T %d\n", landing.Boxes, landing.Boxes)
63 fmt.Fprintf(output, "fish %-12T %d\n", landing.Fish, landing.Fish)
69 fmt.Fprintf(output, "fish %-10T %d\n", landing.Fish, landing.Fish)
64 fmt.Fprintf(output, "weight_g %-12T %d\n", landing.WeightG, landing.WeightG)
70 fmt.Fprintf(output, "weight_g %-10T %d\n", landing.WeightG, landing.WeightG)
65 fmt.Fprintf(output, "length_cm %-12T %.1f\n", landing.LengthCm, landing.LengthCm)
71 fmt.Fprintf(output, "length_cm %-10T %.1f\n", landing.LengthCm, landing.LengthCm)
66 fmt.Fprintf(output, "sea_temp %-12T %.2f\n", landing.SeaTemp, landing.SeaTemp)
72 fmt.Fprintf(output, "water_temp %-10T %.2f\n", landing.WaterTemp, landing.WaterTemp)
67 fmt.Fprintf(output, "price %-12T %s\n", landing.Price, landing.Price.String())
73 fmt.Fprintf(output, "on_ice %-10T %t\n", landing.OnIce, landing.OnIce)
68 fmt.Fprintf(output, "on_ice %-12T %t\n", landing.OnIce, landing.OnIce)
74 fmt.Fprintf(output, "landed_at %-10T %s\n", landing.LandedAt, landing.LandedAt.Format(time.RFC3339))
69 fmt.Fprintf(output, "landed_on %-12T %s\n", landing.LandedOn, landing.LandedOn.Format(time.DateOnly))
75 fmt.Fprintf(output, "landed_on %-10T %s\n", landing.LandedOn, landing.LandedOn.Format(time.DateOnly))
70 // TIMESTAMPTZ arrives in the connection's time zone, so print it in UTC.
76 fmt.Fprintf(output, "catch_id %-10T %s\n", landing.CatchID, landing.CatchID)
71 fmt.Fprintf(output, "landed_at %-12T %s\n", landing.LandedAt, landing.LandedAt.UTC().Format(time.RFC3339))
77 fmt.Fprintf(output, "auction_lot %-10T %s\n", landing.AuctionLot, landing.AuctionLot)
72 fmt.Fprintf(output, "harbour_ip %-12T %v\n", landing.HarbourIP, landing.HarbourIP)
73 // %T prints []byte as []uint8, since byte is an alias for uint8.
78 // %T prints []byte as []uint8, since byte is an alias for uint8.
74 fmt.Fprintf(output, "tag %-12T % x\n", landing.Tag, landing.Tag)
79 fmt.Fprintf(output, "tag %-10T % x\n", landing.Tag, landing.Tag)
75 fmt.Fprintf(output, "notes %-12T %s\n", landing.Notes, *landing.Notes)
80 fmt.Fprintf(output, "notes %-10T %s\n", landing.Notes, *landing.Notes)
76 return nil
81 return nil
77}
82}

Go type differences

The same SQL types map to different Go types, because each database reports them differently.

QueryFieldTimescaleDBDuckDB
RecordLandingparameter length_cmfloat32float64
GetLandingcolumn length_cmfloat32float64