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, v.precision, v.commission_precision, a.id
        FROM (
	    SELECT DISTINCT ON (base_name) name, (base_precision) "precision", (base_commission_precision) commission_precision FROM val
	    UNION
	    SELECT DISTINCT ON (quote_name) name, (quote_precision) "precision", (quote_commission_precision) commission_precision 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, "precision", commission_precision, created_at)
         SELECT name, "precision", commission_precision, now()
         FROM typ_asset
         WHERE id IS NULL
         ON CONFLICT (name) DO UPDATE
             SET created_at = EXCLUDED.created_at WHERE a.created_at <> EXCLUDED.created_at
         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)
);

ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
CONTEXT:  SQL function "fn_insert_symbol_record" statement 1


TABLE asset;


id
name
precision
commission_precision
created_at



SELECT 0


TABLE exchange;


id
name
created_at



SELECT 0


TABLE symbol;


id
name
exchange_id
base_asset_id
quote_asset_id
created_at



SELECT 0