Postgresql – Equivalent of DB2 function “decimal(numeric expression, precision, scale)” in PostgreSQL

Yes, hacking into the catalog is bad. Reason #1 is that if you upgrade to new version and forget to move the hack, things start breaking. Just running pg_dump and loading to the same version on another instance will also lose the hack. There's also always the chance that a new version of Postgres will change so much that your hack is now not possible and force you to go back and re-engineer.

Overriding with your own function is the correct way to go.

To reproduce the problem:

SELECT *, (CASE
    WHEN IsGun=1 THEN CEILING(Price1Avg)
    ELSE Price1 END)
FROM (
    SELECT UPC, IsGun, Price1,
           AVG(CAST(Price1 AS numeric(8, 2))) OVER (PARTITION BY UPC) AS Price1Avg
    FROM (
        VALUES ('A', 0, 14.99),
               ('B', 0, 29.99),
               ('C', 1, 319.00),
               ('D', 1, 314.00)
        ) AS x(UPC, IsGun, Price1)
    ) AS sub;

What happens here is that CEILING(PQ.Price1Avg) produces a numeric(38, 0).

According to the documentation, the output type of CEILING() is of the same base datatype as the input, although the scale (the number of decimals) may change, which is what happens here.

• The AVG() function, in my tests, returns numeric(38, 6).
• The CEILING() function on that column, however, outputs numeric(38, 0):

To verify:

SELECT CEILING(CAST(123.45 AS numeric(38, 6)))

As a workaround, you could explicitly convert the output of the CEILING() function, which should give you the correct results:

SELECT *, (CASE
    WHEN IsGun=1 THEN CAST(CEILING(Price1Avg) AS numeric(8, 2)) -- Explicit CAST.
    ELSE Price1 END)
FROM (
    SELECT UPC, IsGun, Price1,
           AVG(CAST(Price1 AS numeric(8, 2))) OVER (PARTITION BY UPC) AS Price1Avg
    FROM (
        VALUES ('A', 0, 14.99),
               ('B', 0, 29.99),
               ('C', 1, 319.00),
               ('D', 1, 314.00)
        ) AS x(UPC, IsGun, Price1)
    ) AS sub;