Compare · 011

Optional filters: PostgreSQL and ClickHouse

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

Compare PostgreSQL withSQLite CockroachDB TimescaleDB MySQL MariaDB DuckDB ClickHouse

Configuration

sgl.yamldiffers

PostgreSQL
ClickHouse
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: trips
4  - name: trips
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_trips.up.sqldiffers

PostgreSQL
ClickHouse
1CREATE TABLE trips (
1CREATE TABLE trips (
2    id           BIGINT  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
2    id           UInt64,
3    name         TEXT    NOT NULL,
3    name         String,
4    destination  TEXT    NOT NULL,
4    destination  String,
5    price_pence  BIGINT  NOT NULL,
5    price_pence  UInt32,
6    dogs_welcome BOOLEAN NOT NULL
6    dogs_welcome Bool
7);
7) ENGINE = MergeTree() ORDER BY id;

db/migrations/0001_trips.down.sqlidentical

PostgreSQL
ClickHouse
1DROP TABLE trips;
1DROP TABLE trips;

Queries

AddTripdiffers

PostgreSQL · db/queries/trips.sql
ClickHouse · db/queries/trips.sql
1-- sgl.query(AddTrip, exec)
1-- sgl.query(AddTrip, exec)
2INSERT INTO trips (name, destination, price_pence, dogs_welcome) VALUES ($1, $2, $3, $4);
2INSERT INTO trips (id, name, destination, price_pence, dogs_welcome)
3VALUES ({id:UInt64}, {name:String}, {destination:String}, {price_pence:UInt32}, {dogs_welcome:Bool});

SearchTripsdiffers

PostgreSQL · db/queries/trips.sql
ClickHouse · db/queries/trips.sql
1-- sgl.query(SearchTrips, many)
1-- sgl.query(SearchTrips, many)
2-- $1 as sgl.param(destination, optional: true)
2-- {destination:Nullable(String)} as sgl.param(destination, optional: true)
3-- $2 as sgl.param(max_price, optional: true)
3-- {max_price:Nullable(UInt32)} as sgl.param(max_price, optional: true)
4-- $3 as sgl.param(dogs_welcome, optional: true)
4-- {dogs_welcome:Nullable(Bool)} as sgl.param(dogs_welcome, optional: true)
5SELECT id, name, destination, price_pence, dogs_welcome
5SELECT id, name, destination, price_pence, dogs_welcome
6FROM trips
6FROM trips
7WHERE ($1::text IS NULL OR destination = $1)
7WHERE ({destination:Nullable(String)} IS NULL OR destination = {destination:Nullable(String)})
8  AND ($2::bigint IS NULL OR price_pence <= $2)
8  AND ({max_price:Nullable(UInt32)} IS NULL OR price_pence <= {max_price:Nullable(UInt32)})
9  AND ($3::boolean IS NULL OR dogs_welcome = $3)
9  AND ({dogs_welcome:Nullable(Bool)} IS NULL OR dogs_welcome = {dogs_welcome:Nullable(Bool)})
10ORDER BY price_pence, id;
10ORDER BY price_pence, id;

What sgl generates

AddTripdiffers

PostgreSQL
ClickHouse
1func (queries *Queries) AddTrip(ctx context.Context, params AddTripParams) error
1func (queries *Queries) AddTrip(ctx context.Context, params AddTripParams) error
2
2
3type AddTripParams struct {
3type AddTripParams struct {
4	ID          uint64
4	Name        string
5	Name        string
5	Destination string
6	Destination string
6	PricePence  int64
7	PricePence  uint32
7	DogsWelcome bool
8	DogsWelcome bool
8}
9}

SearchTripsdiffers

PostgreSQL
ClickHouse
1func (queries *Queries) SearchTrips(ctx context.Context, params SearchTripsParams) ([]SearchTripsRow, error)
1func (queries *Queries) SearchTrips(ctx context.Context, params SearchTripsParams) ([]SearchTripsRow, error)
2
2
3type SearchTripsParams struct {
3type SearchTripsParams struct {
4	Destination *string
4	Destination *string
5	MaxPrice    *int64
5	MaxPrice    *uint32
6	DogsWelcome *bool
6	DogsWelcome *bool
7}
7}
8
8
9type SearchTripsRow struct {
9type SearchTripsRow struct {
10	ID          int64  `json:"id"`
10	ID          uint64 `json:"id"`
11	Name        string `json:"name"`
11	Name        string `json:"name"`
12	Destination string `json:"destination"`
12	Destination string `json:"destination"`
13	PricePence  int64  `json:"price_pence"`
13	PricePence  uint32 `json:"price_pence"`
14	DogsWelcome bool   `json:"dogs_welcome"`
14	DogsWelcome bool   `json:"dogs_welcome"`
15}
15}

The program

example.godiffers

PostgreSQL
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	"strings"
8	"strings"
9
9
10	"seagull.sh/seagull/examples/postgres/011_optional_filters/internal/store"
10	"seagull.sh/seagull/examples/clickhouse/011_optional_filters/internal/store"
11)
11)
12
12
13// run searches boat trips four ways with one query. Each filter in SearchTrips is written
13// run searches boat trips four ways with one query. Each filter in SearchTrips is written
14// as ($1::text IS NULL OR destination = $1), and its binding says optional: true, which
14// as ({destination:Nullable(String)} IS NULL OR destination =
15// makes the Params field a pointer. A nil pointer is sent as NULL, so $1::text IS NULL is
15// {destination:Nullable(String)}), and its binding line says optional: true, which makes
16// true and that filter matches every row; a set pointer applies the filter. The SQL never
16// the Params field a pointer. A nil pointer is sent as NULL, so the IS NULL test is true
17// and that filter matches every row; a set pointer applies the filter. The SQL never
17// changes, only the values bound to it.
18// changes, only the values bound to it.
18//
19//
19// PostgreSQL needs the cast: in $1 IS NULL alone the server cannot tell the parameter's
20// The placeholder's type must be Nullable(T). With {destination:String} the server reads
20// type and refuses to prepare the statement. The cast must match the column, so the
21// the NULL marker as text, the IS NULL test is false, and a search left open quietly
21// filters use ::text, ::bigint and ::boolean.
22// matches nothing. Nullable(T) alone already makes the field a pointer; optional: true
23// says the filter may be left out on purpose.
22//
24//
23// Takes database (*sql.DB) which is a migrated database.
25// Takes database (*sql.DB) which is a migrated database.
24// Takes output (io.Writer) which receives each search's trips.
26// Takes output (io.Writer) which receives each search's trips.
25//
27//
26// Returns error when a query fails.
28// Returns error when a query fails.
27func run(ctx context.Context, database *sql.DB, output io.Writer) error {
29func run(ctx context.Context, database *sql.DB, output io.Writer) error {
28	queries := store.New(database)
30	queries := store.New(database)
29
31
30	trips := []store.AddTripParams{
32	trips := []store.AddTripParams{
31		{Name: "Seal spotting", Destination: "Gull Rock", PricePence: 1500, DogsWelcome: true},
33		{ID: 1, Name: "Seal spotting", Destination: "Gull Rock", PricePence: 1500, DogsWelcome: true},
32		{Name: "Puffin cruise", Destination: "Puffin Island", PricePence: 2500, DogsWelcome: false},
34		{ID: 2, Name: "Puffin cruise", Destination: "Puffin Island", PricePence: 2500, DogsWelcome: false},
33		{Name: "Mackerel fishing", Destination: "Gull Rock", PricePence: 2000, DogsWelcome: false},
35		{ID: 3, Name: "Mackerel fishing", Destination: "Gull Rock", PricePence: 2000, DogsWelcome: false},
34		{Name: "Sunset sail", Destination: "the bay", PricePence: 1200, DogsWelcome: true},
36		{ID: 4, Name: "Sunset sail", Destination: "the bay", PricePence: 1200, DogsWelcome: true},
35	}
37	}
36	for _, trip := range trips {
38	for _, trip := range trips {
37		if err := queries.AddTrip(ctx, trip); err != nil {
39		if err := queries.AddTrip(ctx, trip); err != nil {
38			return fmt.Errorf("adding %s: %w", trip.Name, err)
40			return fmt.Errorf("adding %s: %w", trip.Name, err)
39		}
41		}
40	}
42	}
41
43
42	searches := []struct {
44	searches := []struct {
43		label      string
45		label      string
44		parameters store.SearchTripsParams
46		parameters store.SearchTripsParams
45	}{
47	}{
46		{label: "no filters", parameters: store.SearchTripsParams{}},
48		{label: "no filters", parameters: store.SearchTripsParams{}},
47		{label: "to Gull Rock", parameters: store.SearchTripsParams{Destination: new("Gull Rock")}},
49		{label: "to Gull Rock", parameters: store.SearchTripsParams{Destination: new("Gull Rock")}},
48		{label: "£15 or less", parameters: store.SearchTripsParams{MaxPrice: new(int64(1500))}},
50		{label: "£15 or less", parameters: store.SearchTripsParams{MaxPrice: new(uint32(1500))}},
49		{label: "to Gull Rock with a dog", parameters: store.SearchTripsParams{Destination: new("Gull Rock"), DogsWelcome: new(true)}},
51		{label: "to Gull Rock with a dog", parameters: store.SearchTripsParams{Destination: new("Gull Rock"), DogsWelcome: new(true)}},
50	}
52	}
51	for _, search := range searches {
53	for _, search := range searches {
52		found, err := queries.SearchTrips(ctx, search.parameters)
54		found, err := queries.SearchTrips(ctx, search.parameters)
53		if err != nil {
55		if err != nil {
54			return fmt.Errorf("searching %s: %w", search.label, err)
56			return fmt.Errorf("searching %s: %w", search.label, err)
55		}
57		}
56		names := make([]string, 0, len(found))
58		names := make([]string, 0, len(found))
57		for _, trip := range found {
59		for _, trip := range found {
58			names = append(names, trip.Name)
60			names = append(names, trip.Name)
59		}
61		}
60		fmt.Fprintf(output, "%-24s %s\n", search.label+":", strings.Join(names, ", "))
62		fmt.Fprintf(output, "%-24s %s\n", search.label+":", strings.Join(names, ", "))
61	}
63	}
62	return nil
64	return nil
63}
65}

Go type differences

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

Query	Field	PostgreSQL	ClickHouse
AddTrip	parameter `price_pence`	`int64`	`uint32`
SearchTrips	parameter `max_price`	`*int64`	`*uint32`
SearchTrips	column `id`	`int64`	`uint64`
SearchTrips	column `price_pence`	`int64`	`uint32`