Compare · 008

Arrays and lists as typed slices: DuckDB and PostgreSQL

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

Compare DuckDB withPostgreSQL CockroachDB TimescaleDB ClickHouse

Configuration

sgl.yamldiffers

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

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

db/migrations/0001_voyages.down.sqldiffers

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

Queries

LogVoyageidentical

DuckDB · 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

DuckDB · 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;

VoyagesCallingAtdiffers

DuckDB · 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, type: varchar)
2-- $1 as sgl.param(port)
3SELECT boat, ports[1] AS first_port, array_length(ports) AS stops
3SELECT boat, ports[1] AS first_port, cardinality(ports) AS stops
4FROM voyages
4FROM voyages
5WHERE $1 = ANY(ports)
5WHERE $1::text = ANY(ports)
6ORDER BY id;
6ORDER BY id;

VoyagesPerBoatidentical

DuckDB · 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

LogVoyagediffers

DuckDB
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   []int32
6	CatchKg   []*int64
7	Sightings []string
7	Sightings []*string
8}
8}

ListVoyagesdiffers

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

VoyagesCallingAtdiffers

DuckDB
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     int64   `json:"stops"`
6	Stops     int32   `json:"stops"`
7}
7}

VoyagesPerBoatidentical

DuckDB
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.godiffers

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