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!

Answer 1

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