Convert byte to certain unit

Question

What's the function to convert byte value to certain unit in PostgreSQL-14?

for example:

Byte     Convert   Converted
Value    Unit      Value
-----    ----      -----
1        MB        0.000001
3000000  GB        0.0029296875 

Answer 1

If your units are really stored that way, you can use pg_size_bytes() to convert the unit into the number of bytes. That value can then be used to divide the input value:

select byte_value, 
       unit,
       pg_size_bytes('1'||unit),
       byte_value::numeric / pg_size_bytes('1'||unit) as converted
from the_table;

Note that this returns slightly different values because pg_size_bytes() works with MiB, so 1MB = 1048576, not 1000000