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-05 12:32:50Z

11

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 5, 2023 at 12:32

answered Aug 6, 2018 at 14:23

Karl Bartel

3,4441 gold badge31 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shiva127 Over a year ago

You should use floor(log(abs(n))) so negatives numbers works too.

Adam Cypher Over a year ago

select case when n is null then null when n = 0 then 0 else round(n, digits - 1 - floor(log(abs(n)))::int) end;

Karl Bartel Over a year ago

@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.

Laurenz Albe · Accepted Answer · 2018-02-21 08:44:48Z

1

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.

answered Feb 21, 2018 at 8:44

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

2 Comments

Rakaziwi Over a year ago

wow, thank you brother, but how can i get just the important number only, without the zeros? 5.4300000000000000 -> 5.43

Laurenz Albe Over a year ago

Cast to text and apply trim(trailing '0' from ...) to the result.

mosh · Accepted Answer · 2020-11-09 16:54:03Z

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 9, 2020 at 16:54

mosh

1,57818 silver badges17 bronze badges

Collectives™ on Stack Overflow

Postgresql rounding to significant figures

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related