postgresqlmathpostgresql-9.6significant-digits

Postgresql rounding to significant figures


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!


Solution

  • 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: