Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE asset
(
    id                   serial PRIMARY KEY,
    name                 character varying(16) unique,
    "precision"            smallint,
    commission_precision smallint,
    created_at           TIMESTAMPTZ
);

CREATE TABLE


CREATE TABLE exchange
(
    id                   serial PRIMARY KEY,
    name                 character varying(32) unique,
    created_at           TIMESTAMPTZ
);

CREATE TABLE


CREATE TABLE symbol
(
    id            serial primary key,
    name          character varying(32),
    exchange_id    int REFERENCES exchange(id),
    base_asset_id int REFERENCES asset (id),
    quote_asset_id int REFERENCES asset (id),
    created_at    TIMESTAMPTZ,
    unique (name, exchange_id)
);

CREATE TABLE


CREATE TYPE symbol_record AS
(
    name                       character varying(32),
    exchange                   character varying(64),

    base_name                  character varying(16),
    base_precision             smallint,
    base_commission_precision  smallint,
    
    quote_name                 character varying(16),
    quote_precision            smallint,
    quote_commission_precision smallint
    
);

CREATE TYPE


CREATE OR REPLACE FUNCTION fn_insert_symbol_record(VARIADIC _val symbol_record[])
    RETURNS void
    LANGUAGE sql AS
$func$
WITH val AS (SELECT * FROM unnest(_val)),

     typ_asset AS (SELECT v.name, a.id
        FROM (
	    SELECT DISTINCT (base_name) name FROM val
	    UNION
	    SELECT DISTINCT (quote_name) name FROM val
	    ) v
	    LEFT JOIN asset a USING (name)
     ),
	typ_exch AS (SELECT v.name, e.id
        FROM (
	    SELECT DISTINCT exchange name FROM val
	    ) v                                   
	    LEFT JOIN exchange e USING (name)
     ),
     ins_asset AS (INSERT INTO asset AS a (name, created_at)
         SELECT name, now()
         FROM typ_asset
         WHERE id IS NULL
         ON CONFLICT (name) DO UPDATE
             SET name = EXCLUDED.name
				   WHERE a.name <> EXCLUDED.name
         RETURNING a.name, a.id
     ),
	ins_exch AS (INSERT INTO exchange AS e (name, created_at)
         SELECT name, now()
         FROM typ_exch
         WHERE id IS NULL
         ON CONFLICT (name) DO UPDATE
             SET name = EXCLUDED.name
         RETURNING e.name, e.id
     )
INSERT INTO symbol AS s (name, exchange_id, base_asset_id, quote_asset_id, created_at)
SELECT v.name, COALESCE(te.id, ie.id), COALESCE(tb.id, ib.id), COALESCE(tq.id, iq.id), now()
FROM val v
    LEFT JOIN typ_asset tb ON tb.name = v.base_name
    LEFT JOIN ins_asset ib ON ib.name = v.base_name
    LEFT JOIN typ_asset tq ON tq.name = v.quote_name
    LEFT JOIN ins_asset iq ON iq.name = v.quote_name
    LEFT JOIN typ_exch te ON te.name = v.exchange
    LEFT JOIN ins_exch ie ON ie.name = v.exchange
ON CONFLICT (name, exchange_id) DO UPDATE
    SET base_asset_id = EXCLUDED.base_asset_id
WHERE s.base_asset_id <> EXCLUDED.base_asset_id
$func$;

CREATE FUNCTION


SELECT fn_insert_symbol_record(
	('ETHBTC',  'Binance',  'ETH', 5::smallint, 5::smallint,         'BTC',  3::smallint, 3::smallint),
    ('ETHUSDT', 'Binance',  'ETH', 5::smallint, 5::smallint,         'USDT', 4::smallint, 4::smallint),
    ('BTCUSDT', 'Binance',  'BTC', 3::smallint, 3::smallint,         'USDT', 4::smallint, 4::smallint)
);


fn_insert_symbol_record


null



SELECT 1


TABLE exchange;


id
name
created_at


1
Binance
2023-08-28 16:49:11.36154+01



SELECT 1


TABLE asset;


id
name
precision
commission_precision
created_at


1
ETH
null
null
2023-08-28 16:49:11.36154+01

2
BTC
null
null
2023-08-28 16:49:11.36154+01

3
USDT
null
null
2023-08-28 16:49:11.36154+01



SELECT 3


TABLE symbol;


id
name
exchange_id
base_asset_id
quote_asset_id
created_at


1
ETHBTC
1
1
2
2023-08-28 16:49:11.36154+01

2
BTCUSDT
1
2
3
2023-08-28 16:49:11.36154+01

3
ETHUSDT
1
1
3
2023-08-28 16:49:11.36154+01



SELECT 3

id	name	precision	commission_precision	created_at
1	ETH	null	null	2023-08-28 16:49:11.36154+01
2	BTC	null	null	2023-08-28 16:49:11.36154+01
3	USDT	null	null	2023-08-28 16:49:11.36154+01

id	name	exchange_id	base_asset_id	quote_asset_id	created_at
1	ETHBTC	1	1	2	2023-08-28 16:49:11.36154+01
2	BTCUSDT	1	2	3	2023-08-28 16:49:11.36154+01
3	ETHUSDT	1	1	3	2023-08-28 16:49:11.36154+01