{"id":3205,"date":"2016-02-25T22:19:34","date_gmt":"2016-02-25T22:19:34","guid":{"rendered":"http:\/\/www.garysieling.com\/blog\/?p=3205"},"modified":"2016-02-25T22:19:34","modified_gmt":"2016-02-25T22:19:34","slug":"postgres-significant-figures-pg_size_pretty","status":"publish","type":"post","link":"https:\/\/www.garysieling.com\/blog\/postgres-significant-figures-pg_size_pretty\/","title":{"rendered":"Rounding to significant figures in Postgres"},"content":{"rendered":"

If you want to round numbers to a specified number of significant figures, you can do this with the logarithm functions.<\/p>\n

For example, rounding the following numbers to two digits would do the following:<\/p>\n

\n10;10\n15;15\n105;100\n109;100\n997;990\n105050;100000\n123456;120000\n193456;190000\n<\/pre>\n
Here is the SQL – note the “-1” needs to be off by one from how many digits you want: -1 gives you two digits.<\/p>\n
\nwith test as (\n  select 10 val\n  union all select 15\n  union all select 105\n  union all select 109\n  union all select 997\n  union all select 105050\n  union all select 123456\n  union all select 193456\n)\nselect \ncontent_size,\nfloor(val\/\n     (10 ^ floor(log(val)-1))) * \n  (10 ^ floor(log(val)-1))\nfrom test\n<\/pre>\n
If you want to use this with pg_size_pretty you’ll also need to cast it:<\/p>\n
\nwith test as (\n  select 10 val\n  union all select 15\n  union all select 105\n  union all select 109\n  union all select 997\n  union all select 105050\n  union all select 123456\n  union all select 193456\n)\nselect \n  content_size,\n  pg_size_pretty(\n    (floor(content_size \/\n     (10 ^ floor(log(val)-1))) * \n    (10 ^ floor(log(val)-1)))::bigint)\nfrom test\n<\/pre>\n
This would give you:<\/p>\n
\n10;\"10 bytes\"\n15;\"15 bytes\"\n105;\"100 bytes\"\n109;\"100 bytes\"\n997;\"990 bytes\"\n105050;\"98 kB\"\n123456;\"117 kB\"\n193456;\"186 kB\"\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
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 … <\/p>\n
Continue reading “Rounding to significant figures in Postgres”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[4],"tags":[437,523],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t