Postgresql rounding to significant figures

Question

i've tried this significant figures query from this blog (https://www.garysieling.com/blog/postgres-significant-figures-pg_size_pretty).

But it seems have fixed decimal digit on it.

SELECT FLOOR(5.4321/(10 ^ FLOOR(log(5.4321)-1))) * (10 ^ FLOOR(log(5.4321)-1))

The result from the query above is 5.4. How can i achieve query to create these results?

number | sigfig
5.4321 | 5.43
10.987 | 10.9
550.75 | 550
9850.5 | 9850
ect

Thank you for you help brothers!

Karl Bartel · Accepted Answer · 2023-01-05T12:32:50.050

9

You can use the following function to round to a number of significant digits:

CREATE OR REPLACE FUNCTION sig_digits(n anyelement, digits int) 
RETURNS numeric
AS $$
    SELECT CASE
        WHEN n=0 THEN 0
        ELSE round(n, digits - 1 - floor(log(abs(n)))::int)
    END
$$ LANGUAGE sql IMMUTABLE STRICT;

Compared to Laurenz' answer this has the following differences:

the results are rounded, not floored (e.g. sig_digits(15, 1) is 20, not 10)
the function takes arbitrary types as first argument (like floats), not only numerics
it's faster, because it avoids calling log on numeric (which is slow) if possible and only calls log once
it returns pretty results without an unnecessary amount of trailing zeros.
it handles zero inputs

edited Jan 05 '23 at 12:32

answered Aug 06 '18 at 14:23

Karl Bartel

3,244
1
29
28

3

You should use `floor(log(abs(n)))` so negatives numbers works too. – Shiva127 Feb 21 '20 at 10:35
select case when n is null then null when n = 0 then 0 else round(n, digits - 1 - floor(log(abs(n)))::int) end; – Adam Cypher Jan 05 '23 at 00:14
@AdamGering I added handling for the zero case. Nulls work without extra changes in my tests, so I didn't add an extra case for that. – Karl Bartel Jan 05 '23 at 12:34

score 1 · Answer 2 · answered Feb 21 '18 at 08:44

To provide a more general answer to this question, I suggest that you use the following function where the number of significant digits is variable:

CREATE FUNCTION significant_digits(n numeric, digits integer) RETURNS numeric
   LANGUAGE sql IMMUTABLE STRICT AS
'SELECT floor(n / (10 ^ floor(log(n) - digits + 1)))
        * (10 ^ floor(log(n) - digits + 1))';

Then you get the following:

test=> SELECT significant_digits(5.4321, 3);
 significant_digits 
--------------------
 5.4300000000000000
(1 row)

test=> SELECT significant_digits(9.87654, 3);
 significant_digits 
--------------------
 9.8700000000000000
(1 row)

Note that the formula does not round as the blog post claims, but truncate.

wow, thank you brother, but how can i get just the important number only, without the zeros? 5.4300000000000000 -> 5.43 — Rakaziwi, Feb 26 '18 at 02:35
Cast to `text` and apply `trim(trailing '0' from ...)` to the result. — Laurenz Albe, Feb 26 '18 at 07:00

score 0 · Answer 3 · answered Nov 09 '20 at 16:54

0

Example of rounding count(status) to Most significant digit:

select count(status),
  round(count(status) / pow(10, trunc(log(count(status))) ) ) * pow(10, trunc(log(count(status))) ) 
  from  some_table;

Eg. 1234 => 1000
    987  => 900

answered Nov 09 '20 at 16:54

mosh

1,402
15
16

Postgresql rounding to significant figures

3 Answers3