pg_sizeof function?

Question

I love the pg_typeof function to get a type of a value, such as:

SELECT pg_typeof(1), pg_typeof(1.4);
integer  numeric

Is there a way to get the byte size of a value as well, doing something like:

SELECT pg_sizeof(1.4);

I know that for each data type I can go back to the documentation page and try and figure out what is what, but is there a way to do that interactively with a query?

@a_horse_with_no_name amazing, thank you. Out of curiosity, why is numeric at 10 bytes? — David542, Sep 09 '22 at 20:16
https://stackoverflow.com/questions/12394538/calculating-the-size-of-a-column-type-in-postgresql — zac, Sep 09 '22 at 20:17
@David542 you can use NUMERIC(precision, scale) https://www.postgresql.org/docs/current/datatype-numeric.html — zac, Sep 09 '22 at 20:23
@zac how do you calculate the size though of a decimal/numeric? — David542, Sep 09 '22 at 20:24
[As documented in the manual](https://www.postgresql.org/docs/current/datatype-numeric.html) `numeric` is a variable length data type: https://dbfiddle.uk/Zn87j4O3 — , Sep 09 '22 at 21:20
@a_horse_with_no_name is there a way to estimate the size before entering it in? — David542, Sep 09 '22 at 21:34
You mean a function that returns the size of a value without passing a value? how should that work? — , Sep 09 '22 at 21:45
@a_horse_with_no_name no I mean just how to estimate size without calling a function, for example, the smallest number has 8 bytes, up until precision of 10 digits which has 10 bytes, up to... etc. — David542, Sep 09 '22 at 21:46
@a_horse_with_no_name I guess it would be: `The actual storage requirement is two bytes for each group of four decimal digits, plus three to eight bytes overhead.`, but curious about the "three to eight" item. — David542, Sep 09 '22 at 22:57
@David542 I highly recommend this post. https://stackoverflow.com/questions/2966524/calculating-and-saving-space-in-postgresql/7431468#7431468 — jian, Sep 10 '22 at 05:04

score 1 · Answer 1 · answered Sep 09 '22 at 20:23

Using pg_column_size you can do something like:

select v, pg_typeof(v), pg_column_size(v) from (values (1)) _ (v) union ALL
select v, pg_typeof(v), pg_column_size(v) from (values (1.2)) _ (v) union ALL
select v, pg_typeof(v), pg_column_size(v) from (values (1e2)) _ (v)

pg_sizeof function?

1 Answers1