ON CONFLICT DO UPDATE command cannot affect row a second time when trying to pass additional columns from a CTE in postgresql

Question

Based on the SO: https://dba.stackexchange.com/a/46477 answer provided, I have been extending the Function with UPSERT in Postgres 9.5 or later for my own purposes to insert data into multiple referenced tables.

I have the following type that I am passing to the function:

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
);

the following relevant CTE's:

-- select all the distinct base_name and quote_name
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)
),

and

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
),

are working correctly.

However, I am unsure how to return the matching 'precision' and 'commission_precision' columns for each of the base/quote for consumption by the calling insert/select in ins_asset.

I tried:

typ_asset AS (SELECT v.name, v."precision", v.commission_precision, a.id
    FROM (
        SELECT DISTINCT (base_name) name, (base_precision) "precision", (base_commission_precision) commission_precision FROM val
        UNION
        SELECT DISTINCT (quote_name) name, (quote_precision) "precision", (quote_commission_precision) commission_precision FROM val
    ) v
    LEFT JOIN asset a USING (name)
),

and adding the columns accordingly to the ins_asset CTE, but am getting the error:

ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time

I am currently using Postgres 14, and the tables and full function can be seen at: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=bfcb7f7a9a9fcbd8a1b65ec033af8ac2

I am sure it has something to do with the inserting additional data into the asset table (typ_asset / ins_asset) ctes, as it works fine without passing the additional columns (or I am passing them back incorrectly)

And further, yes I am fully aware that I am using a reserved keyword (precision). As this is the business language/terminology I have kept it for now (quoting as necessary), but it will also be fixed.

What is the correct way to return the 'precision' and 'commission_precision' columns, and upsert them into the asset table? The 'name' column in the asset table is unique, and I have tried to set commission_precision instead of name in case the unique column was the cause of the error. that also failed with the same error.

edit (1) per requested by ypercubeᵀᴹ, the full working fiddle without the precision and commission_precision values in the asset table: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=9f7ad113c6c812c1348bbc85eef24661

(2) I'm sorry, yes you are correct, I have been trying many different things to fix the problem and DISTINCT ON was one thing I tried (because I don't fully understand the language), apparently, among several other changes. I have corrected the non working dbfiddle, and that actually fixed the problem.

Answer 1

The problem like due to the subquery:

FROM (
    SELECT DISTINCT (base_name) name, (base_precision) "precision", (base_commission_precision) commission_precision FROM val
    UNION
    SELECT DISTINCT (quote_name) name, (quote_precision) "precision", (quote_commission_precision) commission_precision FROM val
) v

producing rows with same name but different precision or commission_precision.

You'll need to fix it so it produces only one row for each name - according to your requirements. There may be different cases:

• rows with same base_name but different base_precision/base_commision_precision.
• rows with same quote_name but different quote_precision/quote_commision_precision.
• rows where base_name matches quote_name of other rows (and different precision/commision_precision).

Other points:

• the columns do not need to be in parantheses.

• the following code part looks to be doing totally nothing:

    ON CONFLICT (name) DO UPDATE
        SET name = EXCLUDED.name
            WHERE a.name <> EXCLUDED.name