Compare · 008

Arrays and lists as typed slices: TimescaleDB and DuckDB

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

Compare TimescaleDB withPostgreSQL CockroachDB DuckDB ClickHouse

Configuration

sgl.yamldiffers

TimescaleDB
DuckDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: voyages
4  - name: voyages
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_voyages.up.sqldiffers

TimescaleDB
DuckDB
1CREATE SEQUENCE voyage_ids;
2
1CREATE TABLE voyages (
3CREATE TABLE voyages (
2    id        BIGINT   GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
4    id        BIGINT    PRIMARY KEY DEFAULT nextval('voyage_ids'),
3    boat      TEXT     NOT NULL,
5    boat      VARCHAR   NOT NULL,
4    ports     TEXT[]   NOT NULL,
6    ports     VARCHAR[] NOT NULL,
5    catch_kg  BIGINT[] NOT NULL,
7    catch_kg  INTEGER[] NOT NULL,
6    sightings TEXT[]
8    sightings VARCHAR[]
7);
9);
8-- sgl.column(voyages.ports, non_null_elements: true)

db/migrations/0001_voyages.down.sqldiffers

TimescaleDB
DuckDB
1DROP TABLE voyages;
1DROP TABLE voyages;
2DROP SEQUENCE voyage_ids;

Queries

LogVoyageidentical

TimescaleDB · db/queries/voyages.sql
DuckDB · db/queries/voyages.sql
1-- sgl.query(LogVoyage, exec)
1-- sgl.query(LogVoyage, exec)
2INSERT INTO voyages (boat, ports, catch_kg, sightings) VALUES ($1, $2, $3, $4);
2INSERT INTO voyages (boat, ports, catch_kg, sightings) VALUES ($1, $2, $3, $4);

ListVoyagesidentical

TimescaleDB · db/queries/voyages.sql
DuckDB · db/queries/voyages.sql
1-- sgl.query(ListVoyages, many)
1-- sgl.query(ListVoyages, many)
2SELECT id, boat, ports, catch_kg, sightings FROM voyages ORDER BY id;
2SELECT id, boat, ports, catch_kg, sightings FROM voyages ORDER BY id;

VoyagesCallingAtdiffers

TimescaleDB · db/queries/voyages.sql
DuckDB · db/queries/voyages.sql
1-- sgl.query(VoyagesCallingAt, many)
1-- sgl.query(VoyagesCallingAt, many)
2-- $1 as sgl.param(port)
2-- $1 as sgl.param(port, type: varchar)
3SELECT boat, ports[1] AS first_port, cardinality(ports) AS stops
3SELECT boat, ports[1] AS first_port, array_length(ports) AS stops
4FROM voyages
4FROM voyages
5WHERE $1::text = ANY(ports)
5WHERE $1 = ANY(ports)
6ORDER BY id;
6ORDER BY id;

VoyagesPerBoatidentical

TimescaleDB · db/queries/voyages.sql
DuckDB · db/queries/voyages.sql
1-- sgl.query(VoyagesPerBoat, many)
1-- sgl.query(VoyagesPerBoat, many)
2SELECT boat, array_agg(id ORDER BY id) AS voyages
2SELECT boat, array_agg(id ORDER BY id) AS voyages
3FROM voyages
3FROM voyages
4GROUP BY boat
4GROUP BY boat
5ORDER BY boat;
5ORDER BY boat;

What sgl generates

LogVoyagediffers

TimescaleDB
DuckDB
1func (queries *Queries) LogVoyage(ctx context.Context, params LogVoyageParams) error
1func (queries *Queries) LogVoyage(ctx context.Context, params LogVoyageParams) error
2
2
3type LogVoyageParams struct {
3type LogVoyageParams struct {
4	Boat      string
4	Boat      string
5	Ports     []string
5	Ports     []string
6	CatchKg   []*int64
6	CatchKg   []int32
7	Sightings []*string
7	Sightings []string
8}
8}

ListVoyagesdiffers

TimescaleDB
DuckDB
1func (queries *Queries) ListVoyages(ctx context.Context) ([]ListVoyagesRow, error)
1func (queries *Queries) ListVoyages(ctx context.Context) ([]ListVoyagesRow, error)
2
2
3type ListVoyagesRow struct {
3type ListVoyagesRow struct {
4	ID        int64     `json:"id"`
4	ID        int64     `json:"id"`
5	Boat      string    `json:"boat"`
5	Boat      string    `json:"boat"`
6	Ports     []string  `json:"ports"`
6	Ports     []*string `json:"ports"`
7	CatchKg   []*int64  `json:"catch_kg"`
7	CatchKg   []*int32  `json:"catch_kg"`
8	Sightings []*string `json:"sightings"`
8	Sightings []*string `json:"sightings"`
9}
9}

VoyagesCallingAtdiffers

TimescaleDB
DuckDB
1func (queries *Queries) VoyagesCallingAt(ctx context.Context, port string) ([]VoyagesCallingAtRow, error)
1func (queries *Queries) VoyagesCallingAt(ctx context.Context, port string) ([]VoyagesCallingAtRow, error)
2
2
3type VoyagesCallingAtRow struct {
3type VoyagesCallingAtRow struct {
4	Boat      string  `json:"boat"`
4	Boat      string  `json:"boat"`
5	FirstPort *string `json:"first_port"`
5	FirstPort *string `json:"first_port"`
6	Stops     int32   `json:"stops"`
6	Stops     int64   `json:"stops"`
7}
7}

VoyagesPerBoatidentical

TimescaleDB
DuckDB
1func (queries *Queries) VoyagesPerBoat(ctx context.Context) ([]VoyagesPerBoatRow, error)
1func (queries *Queries) VoyagesPerBoat(ctx context.Context) ([]VoyagesPerBoatRow, error)
2
2
3type VoyagesPerBoatRow struct {
3type VoyagesPerBoatRow struct {
4	Boat    string  `json:"boat"`
4	Boat    string  `json:"boat"`
5	Voyages []int64 `json:"voyages"`
5	Voyages []int64 `json:"voyages"`
6}
6}

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	"fmt"
6	"fmt"
7	"io"
7	"io"
8
8
9	"seagull.sh/seagull/examples/postgres/008_arrays/internal/store"
9	"seagull.sh/seagull/examples/duckdb/008_arrays/internal/store"
10)
10)
11
11
12// run logs three fishing voyages and reads them back. An array column becomes a typed Go
12// run logs three fishing voyages and reads them back. A DuckDB list column becomes a
13// slice: TEXT[] gives []*string and BIGINT[] gives []*int64, and array_agg gives a slice
13// typed Go slice: VARCHAR[] gives []*string and INTEGER[] gives []*int32, and array_agg
14// of its argument's type, here []int64: the id column it collects is NOT NULL, so the
14// over the NOT NULL id column gives []int64: the list can hold no NULL, so its elements
15// array can hold no NULL and its elements are plain values. A slice parameter is sent as
15// are plain values.
16// a PostgreSQL array, and a nil slice is sent as NULL.
17//
16//
18// The elements are pointers because PostgreSQL can hold NULL in any array, whatever the
17// The elements are pointers because DuckDB can hold NULL in any list, whatever the
19// column's type says, so a NULL element reads as nil, which elements prints as NULL.
18// column's type says, so a NULL element reads as nil, which elements prints as NULL.
20// Parameters take the same pointers, so LogVoyage takes []*int64 for catch_kg and
19// Parameters keep plain elements, so LogVoyage takes []string and []int32. When the data
21// []*string for sightings, and a nil element writes NULL; values builds them from plain
20// never holds a NULL element, non_null_elements: true opts out, in an sgl.column line of
22// values. When the data never holds a NULL element, non_null_elements: true opts out, in
21// a query or migration or as go.types.non_null_elements in sgl.yaml: the elements then
23// an sgl.column line of a query or migration or as go.types.non_null_elements in
22// read as plain values, and a NULL one fails the read.
24// sgl.yaml: the elements then read and bind as plain values, and a NULL one fails the
25// read. The migration declares it for ports, as a voyage never calls at a NULL port, so
26// Ports is a plain []string in the model, the row and the parameters alike.
27//
23//
28// database/sql cannot scan a PostgreSQL array, so the generated SQL wraps each array
24// A slice parameter is bound as a DuckDB list. duckdb-go would bind a nil slice as an
29// column in to_json() and decodes the JSON into the slice; open internal/store to see it.
25// empty list, so the generated code converts the nullable sightings parameter and binds a
30// A NULL array therefore scans to a nil slice, and an empty array to an empty one.
26// nil slice as NULL, as other drivers do: Salty Chip's Sightings: nil is stored as NULL,
27// and the second Kittiwake voyage's empty slice as an empty list.
31//
28//
32// VoyagesCallingAt searches inside an array with $1::text = ANY(ports). The cast gives
29// The generated SQL selects each list column as it is, and the generated code reads the
33// the parameter its type, for Seagull, which would otherwise leave it as any, and for
30// list duckdb-go returns into the slice; open internal/store to see it. A NULL list
34// CockroachDB, which cannot work it out from ANY. ports[1] reads one element, and is
31// therefore scans to a nil slice, and an empty list to an empty one.
35// *string because a missing element is NULL.
36//
32//
33// VoyagesCallingAt searches inside a list with $1 = ANY(ports), whose parameter is pinned
34// with sgl.param(port, type: varchar) as inference leaves it as any, and reads one
35// element with ports[1], which is *string because a missing element is NULL. DuckDB lists
36// count from 1, like PostgreSQL arrays.
37//
37// Takes database (*sql.DB) which is a migrated database.
38// Takes database (*sql.DB) which is a migrated database.
38// Takes output (io.Writer) which receives the voyages and the searches.
39// Takes output (io.Writer) which receives the voyages and the searches.
39//
40//
40// Returns error when a query fails.
41// Returns error when a query fails.
41func run(ctx context.Context, database *sql.DB, output io.Writer) error {
42func run(ctx context.Context, database *sql.DB, output io.Writer) error {
42	queries := store.New(database)
43	queries := store.New(database)
43
44
44	voyages := []store.LogVoyageParams{
45	voyages := []store.LogVoyageParams{
45		{Boat: "Kittiwake", Ports: []string{"Newlyn", "St Ives", "Mousehole"}, CatchKg: values[int64](120, 85), Sightings: values("dolphin", "basking shark")},
46		{Boat: "Kittiwake", Ports: []string{"Newlyn", "St Ives", "Mousehole"}, CatchKg: []int32{120, 85}, Sightings: []string{"dolphin", "basking shark"}},
46		{Boat: "Salty Chip", Ports: []string{"Penzance", "Mousehole"}, CatchKg: values[int64](40), Sightings: nil},
47		{Boat: "Salty Chip", Ports: []string{"Penzance", "Mousehole"}, CatchKg: []int32{40}, Sightings: nil},
47		{Boat: "Kittiwake", Ports: []string{"Newlyn", "Lamorna"}, CatchKg: values[int64](60, 30, 15), Sightings: []*string{}},
48		{Boat: "Kittiwake", Ports: []string{"Newlyn", "Lamorna"}, CatchKg: []int32{60, 30, 15}, Sightings: []string{}},
48	}
49	}
49	for _, voyage := range voyages {
50	for _, voyage := range voyages {
50		if err := queries.LogVoyage(ctx, voyage); err != nil {
51		if err := queries.LogVoyage(ctx, voyage); err != nil {
51			return fmt.Errorf("logging %s's voyage: %w", voyage.Boat, err)
52			return fmt.Errorf("logging %s's voyage: %w", voyage.Boat, err)
52		}
53		}
53	}
54	}
54
55
55	logged, err := queries.ListVoyages(ctx)
56	logged, err := queries.ListVoyages(ctx)
56	if err != nil {
57	if err != nil {
57		return fmt.Errorf("listing voyages: %w", err)
58		return fmt.Errorf("listing voyages: %w", err)
58	}
59	}
59	for _, voyage := range logged {
60	for _, voyage := range logged {
60		sightings := elements(voyage.Sightings)
61		sightings := elements(voyage.Sightings)
61		if voyage.Sightings == nil {
62		if voyage.Sightings == nil {
62			sightings = "NULL"
63			sightings = "NULL"
63		}
64		}
64		fmt.Fprintf(output, "voyage %d  %-10s  ports %s, catch %s kg, sightings %s\n", voyage.ID, voyage.Boat, voyage.Ports, elements(voyage.CatchKg), sightings)
65		fmt.Fprintf(output, "voyage %d  %-10s  ports %s, catch %s kg, sightings %s\n", voyage.ID, voyage.Boat, elements(voyage.Ports), elements(voyage.CatchKg), sightings)
65	}
66	}
66
67
67	calling, err := queries.VoyagesCallingAt(ctx, "Mousehole")
68	calling, err := queries.VoyagesCallingAt(ctx, "Mousehole")
68	if err != nil {
69	if err != nil {
69		return fmt.Errorf("finding voyages calling at Mousehole: %w", err)
70		return fmt.Errorf("finding voyages calling at Mousehole: %w", err)
70	}
71	}
71	fmt.Fprintln(output, "\ncalling at Mousehole:")
72	fmt.Fprintln(output, "\ncalling at Mousehole:")
72	for _, voyage := range calling {
73	for _, voyage := range calling {
73		fmt.Fprintf(output, "  %-10s  from %s, %d stops\n", voyage.Boat, *voyage.FirstPort, voyage.Stops)
74		fmt.Fprintf(output, "  %-10s  from %s, %d stops\n", voyage.Boat, *voyage.FirstPort, voyage.Stops)
74	}
75	}
75
76
76	perBoat, err := queries.VoyagesPerBoat(ctx)
77	perBoat, err := queries.VoyagesPerBoat(ctx)
77	if err != nil {
78	if err != nil {
78		return fmt.Errorf("grouping voyages by boat: %w", err)
79		return fmt.Errorf("grouping voyages by boat: %w", err)
79	}
80	}
80	fmt.Fprintln(output, "\nvoyages per boat:")
81	fmt.Fprintln(output, "\nvoyages per boat:")
81	for _, boat := range perBoat {
82	for _, boat := range perBoat {
82		fmt.Fprintf(output, "  %-10s  %v\n", boat.Boat, boat.Voyages)
83		fmt.Fprintf(output, "  %-10s  %v\n", boat.Boat, boat.Voyages)
83	}
84	}
84	return nil
85	return nil
85}
86}
86
87
87// elements returns an array's elements as fmt prints a slice, with NULL for each element
88// elements returns a list's elements as fmt prints a slice, with NULL for each element
88// the database holds NULL in.
89// the database holds NULL in.
89//
90//
90// Takes array ([]*T) which holds the elements, nil for a NULL one.
91// Takes list ([]*T) which holds the elements, nil for a NULL one.
91//
92//
92// Returns string which is the elements in brackets, separated by spaces.
93// Returns string which is the elements in brackets, separated by spaces.
93func elements[T any](array []*T) string {
94func elements[T any](list []*T) string {
94	texts := make([]string, 0, len(array))
95	texts := make([]string, 0, len(list))
95	for _, element := range array {
96	for _, element := range list {
96		if element == nil {
97		if element == nil {
97			texts = append(texts, "NULL")
98			texts = append(texts, "NULL")
98			continue
99			continue
99		}
100		}
100		texts = append(texts, fmt.Sprint(*element))
101		texts = append(texts, fmt.Sprint(*element))
101	}
102	}
102	return fmt.Sprint(texts)
103	return fmt.Sprint(texts)
103}
104}
104
105// values returns pointers to copies of plain values, as an array parameter whose elements
106// may be NULL takes them.
107//
108// Takes items (...T) which are the elements.
109//
110// Returns []*T which points at the copies, none of them nil.
111func values[T any](items ...T) []*T {
112	pointed := make([]*T, len(items))
113	for index := range items {
114		pointed[index] = &items[index]
115	}
116	return pointed
117}

Go type differences

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

Query	Field	TimescaleDB	DuckDB
LogVoyage	parameter `catch_kg`	`[]*int64`	`[]int32`
LogVoyage	parameter `sightings`	`[]*string`	`[]string`
ListVoyages	column `ports`	`[]string`	`[]*string`
ListVoyages	column `catch_kg`	`[]*int64`	`[]*int32`
VoyagesCallingAt	column `stops`	`int32`	`int64`