9

How can I create an uint256 data type in Postgres? It looks like they only support up to 8 bytes for integers natively..

They offer decimal and numeric types with user-specified precision. For my app, the values are money, so I would assume I would use numeric over decimal, or does that not matter?

NUMERIC(precision, scale)

So would I use NUMERIC(78, 0)? (2^256 is 78 digits) Or do I need to do NUMERIC(155, 0) and force it to always be >= 0 (2^512, 155 digits, with the extra bit representing the sign)? OR should I be using decimal?

ByteMe
  • 1,159
  • 2
  • 15
  • 28

2 Answers2

16

numeric(78,0) has a max value of 9.999... * 10^77 > 2^256 so that is sufficient.

Thorkil Værge
  • 2,727
  • 5
  • 32
  • 48
4

You can create a domain.

CREATE DOMAIN uint_256 AS NUMERIC NOT NULL
CHECK (VALUE >= 0 AND VALUE < 2^256)
CHECK (SCALE(VALUE) = 0)

This creates a reusable uint_256 datatype which is constrained to be within the 2^256 limit and also prevents rounding errors by only allowing the scale of the number to be 0 (i.e. throws an error with decimal values). There is nothing like NULL in Solidity so the datatype should not be nullable.

Try it: dbfiddle

enriquejr99
  • 530
  • 6
  • 6
  • This approach has the benefit of making the intent more understandable. Looking at a table definition with an amount column, `amount uint256` is easier to understand than `numeric(78,0)`. You can find some drawbacks of using domains here: https://dba.stackexchange.com/questions/152859/what-are-the-drawbacks-of-using-domains-to-store-metadata-about-the-column – Yacc Apr 20 '23 at 10:49