Rounding to significant figures in Postgres

If you want to round numbers to a specified number of significant figures, you can do this with the logarithm functions.

For example, rounding the following numbers to two digits would do the following:

10;10
15;15
105;100
109;100
997;990
105050;100000
123456;120000
193456;190000

Here is the SQL – note the “-1” needs to be off by one from how many digits you want: -1 gives you two digits.

with test as (
  select 10 val
  union all select 15
  union all select 105
  union all select 109
  union all select 997
  union all select 105050
  union all select 123456
  union all select 193456
)
select 
content_size,
floor(val/
     (10 ^ floor(log(val)-1))) * 
  (10 ^ floor(log(val)-1))
from test

If you want to use this with pg_size_pretty you’ll also need to cast it:

with test as (
  select 10 val
  union all select 15
  union all select 105
  union all select 109
  union all select 997
  union all select 105050
  union all select 123456
  union all select 193456
)
select 
  content_size,
  pg_size_pretty(
    (floor(content_size /
     (10 ^ floor(log(val)-1))) * 
    (10 ^ floor(log(val)-1)))::bigint)
from test

This would give you:

10;"10 bytes"
15;"15 bytes"
105;"100 bytes"
109;"100 bytes"
997;"990 bytes"
105050;"98 kB"
123456;"117 kB"
193456;"186 kB"