Compare · 008

Arrays and lists as typed slices: TimescaleDB and PostgreSQL

TimescaleDB and PostgreSQL run the same files for this example: the SQL and the Go are identical.

Compare TimescaleDB withPostgreSQL CockroachDB DuckDB ClickHouse

Configuration

sgl.yamlidentical

TimescaleDB
PostgreSQL
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: voyages
4  - name: voyages
5    dialect: postgres
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
11      driver: database/sql

Migrations

db/migrations/0001_voyages.up.sqlidentical

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

db/migrations/0001_voyages.down.sqlidentical

TimescaleDB
PostgreSQL
1DROP TABLE voyages;
1DROP TABLE voyages;

Queries

LogVoyageidentical

TimescaleDB · db/queries/voyages.sql
PostgreSQL · 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
PostgreSQL · 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;

VoyagesCallingAtidentical

TimescaleDB · db/queries/voyages.sql
PostgreSQL · 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)
3SELECT boat, ports[1] AS first_port, cardinality(ports) AS stops
3SELECT boat, ports[1] AS first_port, cardinality(ports) AS stops
4FROM voyages
4FROM voyages
5WHERE $1::text = ANY(ports)
5WHERE $1::text = ANY(ports)
6ORDER BY id;
6ORDER BY id;

VoyagesPerBoatidentical

TimescaleDB · db/queries/voyages.sql
PostgreSQL · 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

LogVoyageidentical

TimescaleDB
PostgreSQL
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   []*int64
7	Sightings []*string
7	Sightings []*string
8}
8}

ListVoyagesidentical

TimescaleDB
PostgreSQL
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   []*int64  `json:"catch_kg"`
8	Sightings []*string `json:"sightings"`
8	Sightings []*string `json:"sightings"`
9}
9}

VoyagesCallingAtidentical

TimescaleDB
PostgreSQL
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     int32   `json:"stops"`
7}
7}

VoyagesPerBoatidentical

TimescaleDB
PostgreSQL
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.goidentical

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