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