Rounding

[guspower]

Introduction

Despite using ROUND in PostgreSQL I am finding tabular and chart data containing unrounded values.

So while a result set may contain various values like 0.54, 0.61 etc., occasionally there will be an unrounded value e.g. 0.7200000000000001. This makes both tables and charts messy.

To Reproduce

An example is one of the total revenue calculations that pulls data from a jsonb object and reports on it using a window function.

SELECT
    'Total Revenue £M' AS series
    , jsonb_path_query(data, '$.revenue.history[*].turn')::numeric AS x
    , jsonb_path_query(data, '$.revenue.history[*].total')::numeric AS y
FROM
    pricing.proposition
WHERE
    proposition_id = $proposition_id::numeric
ORDER BY x ASC
)
SELECT 
    series,
    x,
    ROUND((sum(y) OVER (PARTITION BY series ORDER BY x) / 1000000)::numeric, 2) AS y
FROM 
    monthly_revenue;

Running this using pgAdmin4 gives me a dataset like:

Actual behavior

Instead I get data containing occasional unrounded values.

Screenshots

Expected behavior

I had expected the use of ROUND to round the values and only data with that precision to be present in either the SQLPage tables or charts (or csv exports).

Version information

• OS: Linux x86_64 6.6.13-gentoo
• Database: PostgreSQL 16.3 on x86_64-pc-linux-gnu
• SQLPage Version: v0.26.0

[lovasoa]

Thanks for the report ! Can you send a minimal reproduction ? That would help with fixing the issue. Ideally something with only static selects that does not require a database, like

select 'chart' as component, 'x' as title, 0 as ymin;
select 22 as x, 0.17 as y;
select 30 as x, 0.32 as y;

You can use the debug component to copy the exact values returned by your database.

[guspower]

A simpler, more reproducible set of examples:

SELECT 
    'chart' AS component
    , 'Rounding' AS title
    , TRUE AS debug
    ;
SELECT generate_series(1, 10) as x, ROUND(RANDOM()::numeric, 2) as y;

SELECT 
    'debug' AS component
    , 'Rounding' AS title
    , TRUE AS debug
    ;
SELECT generate_series(1, 10) as x, ROUND(RANDOM()::numeric, 2) as y;

SELECT 
    'table' as component
    ;
SELECT generate_series(1, 10) as x, ROUND(RANDOM()::numeric, 2) as y;

I don't know if this is a sqlpage bug or a problem between keyboard and chair. Any light you can throw on this would be great.

[lovasoa]

Thanks, I can reproduce it !

[lovasoa]

The issue can be reproduced with a file containing just

select 0.47::numeric;

When opened with sqlpage, this displays 0.47000000000000003