Compare · 005

From SQL types to Go types: TimescaleDB and ClickHouse

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

Compare TimescaleDB withSQLitePostgreSQLMySQLMariaDBDuckDBClickHouse

Configuration

sgl.yamldiffers
TimescaleDB
ClickHouse
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: landings
4 - name: landings
5 dialect: postgres
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
11 driver: database/sql

Migrations

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

Queries

RecordLandingdiffers
TimescaleDB · db/queries/landings.sql
ClickHouse · db/queries/landings.sql
1-- sgl.query(RecordLanding, one)
1-- sgl.query(RecordLanding, exec)
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 (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
TimescaleDB · db/queries/landings.sql
ClickHouse · 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, 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
TimescaleDB
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 CatchID uuid.UUID
4 ID uint64
5 Species string
5 CatchID string
6 Boxes int16
6 Species string
7 Fish int32
7 Boxes uint16
8 WeightG int64
8 Fish uint32
9 LengthCm float32
9 WeightG int64
10 SeaTemp float64
10 LengthCm float32
11 Price apd.Decimal
11 Price string
12 OnIce bool
12 OnIce bool
13 LandedOn time.Time
13 LandedAt time.Time
14 LandedAt time.Time
14 Tag string
15 HarbourIP any
15 Notes *string
16 Tag []byte
17 Notes *string
18}
19
20type RecordLandingRow struct {
21 ID int64 `json:"id"`
22}
16}
GetLandingdiffers
TimescaleDB
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 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 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 float32 `json:"length_cm"`
10 LengthCm float32 `json:"length_cm"`
11 SeaTemp float64 `json:"sea_temp"`
11 Price decimal.Decimal `json:"price"`
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 Tag string `json:"tag"`
15 LandedAt time.Time `json:"landed_at"`
15 Notes *string `json:"notes"`
16 HarbourIP any `json:"harbour_ip"`
17 Tag []byte `json:"tag"`
18 Notes *string `json:"notes"`
19}
16}

The program

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

Go type differences

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

QueryFieldTimescaleDBClickHouse
RecordLandingparameter catch_iduuid.UUIDstring
RecordLandingparameter boxesint16uint16
RecordLandingparameter fishint32uint32
RecordLandingparameter priceapd.Decimalstring
RecordLandingparameter tag[]bytestring
GetLandingparameter idint64uint64
GetLandingcolumn idint64uint64
GetLandingcolumn boxesint16uint16
GetLandingcolumn fishint32uint32
GetLandingcolumn priceapd.Decimaldecimal.Decimal
GetLandingcolumn tag[]bytestring