Compare · 005

From SQL types to Go types: DuckDB and PostgreSQL

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

Compare DuckDB withSQLitePostgreSQLTimescaleDBMySQLMariaDBClickHouse

Configuration

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

Queries

RecordLandingdiffers
DuckDB · db/queries/landings.sql
PostgreSQL · db/queries/landings.sql
1-- sgl.query(RecordLanding, one)
1-- sgl.query(RecordLanding, one)
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)
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)
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
DuckDB · db/queries/landings.sql
PostgreSQL · db/queries/landings.sql
1-- sgl.query(GetLanding, one)
1-- sgl.query(GetLanding, one)
2SELECT id, species, quay, boxes, fish, weight_g, length_cm, water_temp, on_ice, landed_at, landed_on, catch_id, auction_lot, tag, notes
2SELECT id, catch_id, species, boxes, fish, weight_g, length_cm, sea_temp, price, on_ice, landed_on, landed_at, harbour_ip, tag, notes
3FROM landings
3FROM landings
4WHERE id = $1;
4WHERE id = $1;

What sgl generates

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

Go type differences

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

QueryFieldDuckDBPostgreSQL
RecordLandingparameter length_cmfloat64float32
GetLandingcolumn length_cmfloat64float32