postgres - bigint out of range error, which datatype to use for very large numbers

Question

I am trying to store some hashes (originally in hexadecimal) but I cast them to int to save in pgsql but get

select 12347933502038296527::bigint

ERROR:  bigint out of range
********** Error **********
ERROR: bigint out of range
SQL state: 22003

I realize the number is too large for int8type, what data type should I be using instead of int8? would decimal work for my case? any other strategy that would work to save such large numbers?

Answer 1

Bigint's are pretty big. It is unusual to need anything larger than that. In most databases, that is as far as you can go with a native binary representation. Decimal/numeric often allows a slightly larger precision.

Postgres, by contrast, supports numeric/decimal with basically unlimited precision, as explained in the documentation.

The following works directly:

select 12347933502038296527::decimal

If you want to be more specific:

select 12347933502038296527::decimal(20, 0)

That said, often such large numbers are used as ids. In that case, you might want to use a string instead of a numeric representation. A string has the advantage of keeping leading zeros, for instance.