0

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.

4
  • 1
    Your linked fiddle uses SELECT DISTINCT ON (). Your query here shows SELECT DISTINCT. These are different. Jan 7, 2022 at 9:44
  • @a_horse_with_no_name I am sorry, I am just learning postgres and am building off examples, I realize (now) parenthesis are used to represent MULTIPLE columns so yes, they are quite 'useless' here, though I left them in more for readability than anything else. I apologize if that was wrong, but that is the 'why' to your question. I have fixed the jsfiddle and removed them. Jan 7, 2022 at 16:09
  • You shouldn't enclose multiple columns in parentheses either. That will turn those columns into a single column.
    – user1822
    Jan 7, 2022 at 16:29
  • @a_horse_with_no_name oh, that makes sense too... Thank you for pointing that out. I am learning all of this on the fly (I am a developer, not a dba, and only do the database stuff out of necessity), so your tips on this have clarified a few misconceptions I had as well. Jan 7, 2022 at 16:36

1 Answer 1

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
    
3
  • it was 100% the DISTINCT ON vs DISTINCT you mentioned in your question comment... I have also removed the additional redundant parenthesis as per @a_horse_with_no_name, Jan 7, 2022 at 16:22
  • in my use case, the matching precision/commisson_precision should always be the same for the same asset name (and regardless of whether it is base side or quote side) as far as I can tell (but that may change and may be something I have to deal with in the future for sure).. Jan 7, 2022 at 16:28
  • with regards to 'the following code part looks to be doing totally nothing'.. as far as I understand (from the original post I was referring to where I got much of this query/function), the reasoning for that is to prevent race conditions (as it will be called from a multithreaded application), I may be wrong, but that was my understanding of that original post I referenced. Jan 7, 2022 at 16:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.