Compare · 011

Optional filters: ClickHouse and PostgreSQL

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

Compare ClickHouse withSQLite PostgreSQL CockroachDB TimescaleDB MySQL MariaDB DuckDB

Configuration

sgl.yamldiffers

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

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

db/migrations/0001_trips.down.sqlidentical

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

Queries

AddTripdiffers

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

SearchTripsdiffers

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

What sgl generates

AddTripdiffers

ClickHouse
PostgreSQL
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
5	Name        string
4	Name        string
6	Destination string
5	Destination string
7	PricePence  uint32
6	PricePence  int64
8	DogsWelcome bool
7	DogsWelcome bool
9}
8}

SearchTripsdiffers

ClickHouse
PostgreSQL
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    *uint32
5	MaxPrice    *int64
6	DogsWelcome *bool
6	DogsWelcome *bool
7}
7}
8
8
9type SearchTripsRow struct {
9type SearchTripsRow struct {
10	ID          uint64 `json:"id"`
10	ID          int64  `json:"id"`
11	Name        string `json:"name"`
11	Name        string `json:"name"`
12	Destination string `json:"destination"`
12	Destination string `json:"destination"`
13	PricePence  uint32 `json:"price_pence"`
13	PricePence  int64  `json:"price_pence"`
14	DogsWelcome bool   `json:"dogs_welcome"`
14	DogsWelcome bool   `json:"dogs_welcome"`
15}
15}

The program

example.godiffers

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

Go type differences

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

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