Compare · 005

From SQL types to Go types: ClickHouse and TimescaleDB

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

Compare ClickHouse withSQLitePostgreSQLTimescaleDBMySQLMariaDBDuckDB

Configuration

sgl.yamldiffers
ClickHouse
TimescaleDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: landings
4 - name: landings
5 dialect: clickhouse
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
ClickHouse
TimescaleDB
1CREATE TABLE landings (
1CREATE TABLE landings (
2 id UInt64,
2 id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
3 catch_id UUID,
3 catch_id UUID NOT NULL,
4 species String,
4 species TEXT NOT NULL,
5 boxes UInt16,
5 boxes SMALLINT NOT NULL,
6 fish UInt32,
6 fish INTEGER NOT NULL,
7 weight_g Int64,
7 weight_g BIGINT NOT NULL,
8 length_cm Float32,
8 length_cm REAL NOT NULL,
9 price Decimal(9, 2),
9 sea_temp DOUBLE PRECISION NOT NULL,
10 on_ice Bool,
10 price NUMERIC(8, 2) NOT NULL,
11 landed_at DateTime('UTC'),
11 on_ice BOOLEAN NOT NULL,
12 tag FixedString(4),
12 landed_on DATE NOT NULL,
13 notes Nullable(String)
13 landed_at TIMESTAMPTZ NOT NULL,
14) ENGINE = MergeTree() ORDER BY id;
14 harbour_ip INET NOT NULL,
15 tag BYTEA NOT NULL,
16 notes TEXT
17);
db/migrations/0001_landings.down.sqlidentical
ClickHouse
TimescaleDB
1DROP TABLE landings;
1DROP TABLE landings;

Queries

RecordLandingdiffers
ClickHouse · db/queries/landings.sql
TimescaleDB · db/queries/landings.sql
1-- sgl.query(RecordLanding, exec)
1-- sgl.query(RecordLanding, one)
2INSERT INTO landings (id, catch_id, species, boxes, fish, weight_g, length_cm, price, on_ice, landed_at, 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 (
3VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
4 {id:UInt64},
4RETURNING id;
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
ClickHouse · db/queries/landings.sql
TimescaleDB · 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, price, on_ice, landed_at, 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 = {id:UInt64};
4WHERE id = $1;

What sgl generates

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

The program

example.godiffers
ClickHouse
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/clickhouse/005_type_mapping/internal/store"
10 "github.com/cockroachdb/apd/v3"
11 "github.com/google/uuid"
12
13 "seagull.sh/seagull/examples/postgres/005_type_mapping/internal/store"
11)
14)
12
15
13// 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
14// each column. ClickHouse types are exact about width and sign, and Seagull follows them:
17// each column of db/migrations. PostgreSQL's types are exact, so each maps by name:
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.
20//
18//
21// UUID gives uuid.UUID from github.com/google/uuid and Decimal(9, 2) gives
19// - SMALLINT, INTEGER and BIGINT give int16, int32 and int64;
22// decimal.Decimal from github.com/shopspring/decimal, the types clickhouse-go scans them
20// - REAL is single precision and gives float32, while DOUBLE PRECISION gives float64;
23// into. A {price:Decimal(9, 2)} placeholder would make the parameter a decimal.Decimal
21// - NUMERIC gives apd.Decimal from github.com/cockroachdb/apd/v3, which keeps money
24// too; this example passes the price and catch id as text and converts them in SQL with
22// exact, and UUID gives uuid.UUID from github.com/google/uuid;
25// toDecimal32 and toUUID, so it needs nothing beyond the standard library.
23// - DATE and TIMESTAMPTZ give time.Time, and BYTEA gives []byte;
24// - a type Seagull has no mapping for, such as INET, gives any, and the driver decides
25// what it holds: pgx's database/sql driver hands back the address as a string. Give
26// such a column a go_type override to pin it down.
26//
27//
27// Takes database (*sql.DB) which is a migrated database.
28// Takes database (*sql.DB) which is a migrated database.
28// Takes output (io.Writer) which receives one line per column.
29// Takes output (io.Writer) which receives one line per column.
29//
30//
30// Returns error when a query fails.
31// Returns error when a query fails.
31func run(ctx context.Context, database *sql.DB, output io.Writer) error {
32func run(ctx context.Context, database *sql.DB, output io.Writer) error {
32 queries := store.New(database)
33 queries := store.New(database)
33
34
34 if err := queries.RecordLanding(ctx, store.RecordLandingParams{
35 recorded, err := queries.RecordLanding(ctx, store.RecordLandingParams{
35 ID: 1,
36 CatchID: uuid.MustParse("6f1c0b8e-4b2a-4c55-9d7e-2a3f5e8c1d40"),
36 CatchID: "5f0c6c7e-8d0e-4b77-9a51-2c4a6f9e3b10",
37 Species: "mackerel",
37 Species: "mackerel",
38 Boxes: 12,
38 Boxes: 12,
39 Fish: 480,
39 Fish: 480,
40 WeightG: 163_200,
40 WeightG: 163_200,
41 LengthCm: 31.5,
41 LengthCm: 31.5,
42 SeaTemp: 12.25,
42 Price: "412.50",
43 Price: *apd.New(41_280, -2),
43 OnIce: true,
44 OnIce: true,
44 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),
45 Tag: string([]byte{0x04, 0xa2, 0x19, 0xf3}),
46 LandedAt: time.Date(2026, time.May, 1, 5, 30, 0, 0, time.UTC),
46 Notes: new("landed at Newlyn"),
47 HarbourIP: "192.0.2.17",
47 }); err != nil {
48 Tag: []byte{0x04, 0xa2, 0x19, 0xf3},
49 Notes: new("landed at Newlyn"),
50 })
51 if err != nil {
48 return fmt.Errorf("recording a landing: %w", err)
52 return fmt.Errorf("recording a landing: %w", err)
49 }
53 }
50
54
51 landing, err := queries.GetLanding(ctx, 1)
55 landing, err := queries.GetLanding(ctx, recorded.ID)
52 if err != nil {
56 if err != nil {
53 return fmt.Errorf("getting landing 1: %w", err)
57 return fmt.Errorf("getting landing %d: %w", recorded.ID, err)
54 }
58 }
55 fmt.Fprintf(output, "id %-16T %d\n", landing.ID, landing.ID)
59 fmt.Fprintf(output, "id %-12T %d\n", landing.ID, landing.ID)
56 fmt.Fprintf(output, "catch_id %-16T %s\n", landing.CatchID, landing.CatchID)
60 fmt.Fprintf(output, "catch_id %-12T %s\n", landing.CatchID, landing.CatchID)
57 fmt.Fprintf(output, "species %-16T %s\n", landing.Species, landing.Species)
61 fmt.Fprintf(output, "species %-12T %s\n", landing.Species, landing.Species)
58 fmt.Fprintf(output, "boxes %-16T %d\n", landing.Boxes, landing.Boxes)
62 fmt.Fprintf(output, "boxes %-12T %d\n", landing.Boxes, landing.Boxes)
59 fmt.Fprintf(output, "fish %-16T %d\n", landing.Fish, landing.Fish)
63 fmt.Fprintf(output, "fish %-12T %d\n", landing.Fish, landing.Fish)
60 fmt.Fprintf(output, "weight_g %-16T %d\n", landing.WeightG, landing.WeightG)
64 fmt.Fprintf(output, "weight_g %-12T %d\n", landing.WeightG, landing.WeightG)
61 fmt.Fprintf(output, "length_cm %-16T %.1f\n", landing.LengthCm, landing.LengthCm)
65 fmt.Fprintf(output, "length_cm %-12T %.1f\n", landing.LengthCm, landing.LengthCm)
62 fmt.Fprintf(output, "price %-16T %s\n", landing.Price, landing.Price.StringFixed(2))
66 fmt.Fprintf(output, "sea_temp %-12T %.2f\n", landing.SeaTemp, landing.SeaTemp)
63 fmt.Fprintf(output, "on_ice %-16T %t\n", landing.OnIce, landing.OnIce)
67 fmt.Fprintf(output, "price %-12T %s\n", landing.Price, landing.Price.String())
64 fmt.Fprintf(output, "landed_at %-16T %s\n", landing.LandedAt, landing.LandedAt.Format(time.RFC3339))
68 fmt.Fprintf(output, "on_ice %-12T %t\n", landing.OnIce, landing.OnIce)
65 fmt.Fprintf(output, "tag %-16T % x\n", landing.Tag, landing.Tag)
69 fmt.Fprintf(output, "landed_on %-12T %s\n", landing.LandedOn, landing.LandedOn.Format(time.DateOnly))
66 fmt.Fprintf(output, "notes %-16T %s\n", landing.Notes, *landing.Notes)
70 // TIMESTAMPTZ arrives in the connection's time zone, so print it in UTC.
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)
73 // %T prints []byte as []uint8, since byte is an alias for uint8.
74 fmt.Fprintf(output, "tag %-12T % x\n", landing.Tag, landing.Tag)
75 fmt.Fprintf(output, "notes %-12T %s\n", landing.Notes, *landing.Notes)
67 return nil
76 return nil
68}
77}

Go type differences

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

QueryFieldClickHouseTimescaleDB
RecordLandingparameter catch_idstringuuid.UUID
RecordLandingparameter boxesuint16int16
RecordLandingparameter fishuint32int32
RecordLandingparameter pricestringapd.Decimal
RecordLandingparameter tagstring[]byte
GetLandingparameter iduint64int64
GetLandingcolumn iduint64int64
GetLandingcolumn boxesuint16int16
GetLandingcolumn fishuint32int32
GetLandingcolumn pricedecimal.Decimalapd.Decimal
GetLandingcolumn tagstring[]byte