Compare · 005

From SQL types to Go types: DuckDB and ClickHouse

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

Compare DuckDB withSQLitePostgreSQLTimescaleDBMySQLMariaDBClickHouse

Configuration

sgl.yamldiffers
DuckDB
ClickHouse
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: harbour
4 - name: landings
5 dialect: duckdb
5 dialect: clickhouse
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

Migrations

db/migrations/0001_landings.up.sqldiffers
DuckDB
ClickHouse
1CREATE SEQUENCE landing_ids;
2
3CREATE TABLE landings (
1CREATE TABLE landings (
4 id BIGINT PRIMARY KEY DEFAULT nextval('landing_ids'),
2 id UInt64,
5 species VARCHAR NOT NULL,
3 catch_id UUID,
6 quay UTINYINT NOT NULL,
4 species String,
7 boxes SMALLINT NOT NULL,
5 boxes UInt16,
8 fish INTEGER NOT NULL,
6 fish UInt32,
9 weight_g BIGINT NOT NULL,
7 weight_g Int64,
10 length_cm DOUBLE NOT NULL,
8 length_cm Float32,
11 water_temp REAL NOT NULL,
9 price Decimal(9, 2),
12 on_ice BOOLEAN NOT NULL,
10 on_ice Bool,
13 landed_at TIMESTAMP NOT NULL,
11 landed_at DateTime('UTC'),
14 landed_on DATE NOT NULL,
12 tag FixedString(4),
15 catch_id UUID NOT NULL,
13 notes Nullable(String)
16 auction_lot HUGEINT NOT NULL,
14) ENGINE = MergeTree() ORDER BY id;
17 tag BLOB NOT NULL,
18 notes VARCHAR
19);
db/migrations/0001_landings.down.sqldiffers
DuckDB
ClickHouse
1DROP TABLE landings;
1DROP TABLE landings;
2DROP SEQUENCE landing_ids;

Queries

RecordLandingdiffers
DuckDB · db/queries/landings.sql
ClickHouse · db/queries/landings.sql
1-- sgl.query(RecordLanding, one)
1-- sgl.query(RecordLanding, exec)
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 (id, catch_id, species, boxes, fish, weight_g, length_cm, price, on_ice, landed_at, tag, notes)
3VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
3VALUES (
4RETURNING id;
4 {id:UInt64},
5 toUUID({catch_id:String}),
6 {species:String},
7 {boxes:UInt16},
8 {fish:UInt32},
9 {weight_g:Int64},
10 {length_cm:Float32},
11 toDecimal32({price:String}, 2),
12 {on_ice:Bool},
13 {landed_at:DateTime('UTC')},
14 {tag:FixedString(4)},
15 {notes:Nullable(String)}
16);
GetLandingdiffers
DuckDB · db/queries/landings.sql
ClickHouse · 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, price, on_ice, landed_at, tag, notes
3FROM landings
3FROM landings
4WHERE id = $1;
4WHERE id = {id:UInt64};

What sgl generates

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

The program

example.godiffers
DuckDB
ClickHouse
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
12 "github.com/google/uuid"
10 "seagull.sh/seagull/examples/clickhouse/005_type_mapping/internal/store"
13
14 "seagull.sh/seagull/examples/duckdb/005_type_mapping/internal/store"
15)
11)
16
12
17// run records a landing of fish and reads it back, printing the Go type sgl chose for
13// 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:
14// each column. ClickHouse types are exact about width and sign, and Seagull follows them:
15// UInt16, UInt32 and UInt64 give uint16, uint32 and uint64, Int64 gives int64, Float32
16// gives float32, and Bool gives bool. DateTime('UTC') gives time.Time; the generated code
17// sends a time as Unix seconds, so the server keeps its moment whatever the zone, and the
18// column's time zone makes it read back in UTC. FixedString(4) and String both give
19// string, which in ClickHouse holds any bytes, and Nullable(String) gives *string.
19//
20//
20// - SMALLINT, INTEGER and BIGINT give int16, int32 and int64, and the unsigned UTINYINT
21// UUID gives uuid.UUID from github.com/google/uuid and Decimal(9, 2) gives
21// gives uint8;
22// decimal.Decimal from github.com/shopspring/decimal, the types clickhouse-go scans them
22// - DOUBLE gives float64, but REAL is DuckDB's 4-byte float, so it gives float32;
23// into. A {price:Decimal(9, 2)} placeholder would make the parameter a decimal.Decimal
23// - TIMESTAMP and DATE give time.Time, and UUID gives uuid.UUID from
24// too; this example passes the price and catch id as text and converts them in SQL with
24// github.com/google/uuid;
25// toDecimal32 and toUUID, so it needs nothing beyond the standard library.
25// - HUGEINT is a 128-bit integer with no Go equivalent, so it gives *big.Int, the type
26// duckdb-go returns, even when the column is NOT NULL;
27// - BLOB gives []byte, and a nullable VARCHAR gives *string.
28//
26//
29// Takes database (*sql.DB) which is a migrated database.
27// Takes database (*sql.DB) which is a migrated database.
30// Takes output (io.Writer) which receives one line per column.
28// Takes output (io.Writer) which receives one line per column.
31//
29//
32// Returns error when a query fails.
30// Returns error when a query fails.
33func run(ctx context.Context, database *sql.DB, output io.Writer) error {
31func run(ctx context.Context, database *sql.DB, output io.Writer) error {
34 queries := store.New(database)
32 queries := store.New(database)
35
33
36 // The auction lot number has 20 digits, too many for int64 or uint64.
34 if err := queries.RecordLanding(ctx, store.RecordLandingParams{
37 lot, ok := new(big.Int).SetString("20260501000000000001", 10)
35 ID: 1,
38 if !ok {
36 CatchID: "5f0c6c7e-8d0e-4b77-9a51-2c4a6f9e3b10",
39 return errors.New("parsing the auction lot number")
37 Species: "mackerel",
40 }
38 Boxes: 12,
41 recorded, err := queries.RecordLanding(ctx, store.RecordLandingParams{
39 Fish: 480,
42 Species: "mackerel",
40 WeightG: 163_200,
43 Quay: 3,
41 LengthCm: 31.5,
44 Boxes: 12,
42 Price: "412.50",
45 Fish: 480,
43 OnIce: true,
46 WeightG: 163_200,
44 LandedAt: time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),
47 LengthCm: 31.5,
45 Tag: string([]byte{0x04, 0xa2, 0x19, 0xf3}),
48 WaterTemp: 11.25,
46 Notes: new("landed at Newlyn"),
49 OnIce: true,
47 }); err != nil {
50 LandedAt: time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),
51 LandedOn: time.Date(2026, time.May, 1, 0, 0, 0, 0, time.UTC),
52 CatchID: uuid.MustParse("6f1c2a5e-4d3b-4c2a-9e8f-1a2b3c4d5e6f"),
53 AuctionLot: lot,
54 Tag: []byte{0x04, 0xa2, 0x19, 0xf3},
55 Notes: new("landed at Newlyn"),
56 })
57 if err != nil {
58 return fmt.Errorf("recording a landing: %w", err)
48 return fmt.Errorf("recording a landing: %w", err)
59 }
49 }
60
50
61 landing, err := queries.GetLanding(ctx, recorded.ID)
51 landing, err := queries.GetLanding(ctx, 1)
62 if err != nil {
52 if err != nil {
63 return fmt.Errorf("getting landing %d: %w", recorded.ID, err)
53 return fmt.Errorf("getting landing 1: %w", err)
64 }
54 }
65 fmt.Fprintf(output, "id %-10T %d\n", landing.ID, landing.ID)
55 fmt.Fprintf(output, "id %-16T %d\n", landing.ID, landing.ID)
66 fmt.Fprintf(output, "species %-10T %s\n", landing.Species, landing.Species)
56 fmt.Fprintf(output, "catch_id %-16T %s\n", landing.CatchID, landing.CatchID)
67 fmt.Fprintf(output, "quay %-10T %d\n", landing.Quay, landing.Quay)
57 fmt.Fprintf(output, "species %-16T %s\n", landing.Species, landing.Species)
68 fmt.Fprintf(output, "boxes %-10T %d\n", landing.Boxes, landing.Boxes)
58 fmt.Fprintf(output, "boxes %-16T %d\n", landing.Boxes, landing.Boxes)
69 fmt.Fprintf(output, "fish %-10T %d\n", landing.Fish, landing.Fish)
59 fmt.Fprintf(output, "fish %-16T %d\n", landing.Fish, landing.Fish)
70 fmt.Fprintf(output, "weight_g %-10T %d\n", landing.WeightG, landing.WeightG)
60 fmt.Fprintf(output, "weight_g %-16T %d\n", landing.WeightG, landing.WeightG)
71 fmt.Fprintf(output, "length_cm %-10T %.1f\n", landing.LengthCm, landing.LengthCm)
61 fmt.Fprintf(output, "length_cm %-16T %.1f\n", landing.LengthCm, landing.LengthCm)
72 fmt.Fprintf(output, "water_temp %-10T %.2f\n", landing.WaterTemp, landing.WaterTemp)
62 fmt.Fprintf(output, "price %-16T %s\n", landing.Price, landing.Price.StringFixed(2))
73 fmt.Fprintf(output, "on_ice %-10T %t\n", landing.OnIce, landing.OnIce)
63 fmt.Fprintf(output, "on_ice %-16T %t\n", landing.OnIce, landing.OnIce)
74 fmt.Fprintf(output, "landed_at %-10T %s\n", landing.LandedAt, landing.LandedAt.Format(time.RFC3339))
64 fmt.Fprintf(output, "landed_at %-16T %s\n", landing.LandedAt, landing.LandedAt.Format(time.RFC3339))
75 fmt.Fprintf(output, "landed_on %-10T %s\n", landing.LandedOn, landing.LandedOn.Format(time.DateOnly))
65 fmt.Fprintf(output, "tag %-16T % x\n", landing.Tag, landing.Tag)
76 fmt.Fprintf(output, "catch_id %-10T %s\n", landing.CatchID, landing.CatchID)
66 fmt.Fprintf(output, "notes %-16T %s\n", landing.Notes, *landing.Notes)
77 fmt.Fprintf(output, "auction_lot %-10T %s\n", landing.AuctionLot, landing.AuctionLot)
78 // %T prints []byte as []uint8, since byte is an alias for uint8.
79 fmt.Fprintf(output, "tag %-10T % x\n", landing.Tag, landing.Tag)
80 fmt.Fprintf(output, "notes %-10T %s\n", landing.Notes, *landing.Notes)
81 return nil
67 return nil
82}
68}

Go type differences

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

QueryFieldDuckDBClickHouse
RecordLandingparameter boxesint16uint16
RecordLandingparameter fishint32uint32
RecordLandingparameter length_cmfloat64float32
RecordLandingparameter catch_iduuid.UUIDstring
RecordLandingparameter tag[]bytestring
GetLandingparameter idint64uint64
GetLandingcolumn idint64uint64
GetLandingcolumn boxesint16uint16
GetLandingcolumn fishint32uint32
GetLandingcolumn length_cmfloat64float32
GetLandingcolumn tag[]bytestring