Storing large Ethereum numbers in Postgres

Numbers in Ethereum are loooooong.

In fact, ETH values are always defined as 18 decimals. One ETH equals 1000000000000000000 WEI, where WEI is the smalles indivisable unit. To make matters more complicated, ERC-20 tokens can have any arbitrary number of decimals.

We can choose to store values either as ints (150…00) or as decimal numbers (1.5)

In general, it is cleanest and best practice to go with the first approach, storing numbers as integers in their lowest form.

However… the obvious data type for this, BIGINT, turns out to not work that well…

BIGINT isn’t quite big enough

While BigInt() in Javascript works flawslessly for an unlimited amount of digits, but the BIGINT in Postgres caps out at around 19 decimals 😩.

The max ETH value that BIGINT in Postgres can store is around 9.2 ETH.

Option 1: Store numbers as a strings

A foolproof way of storing these values is by storing them as strings.

Strings are simple, and work great, but only as long as you are not processing the numbers on the DB-level to do aggregations (SUM(X)) or queries with comparisons (value > XXX).

Option 2: Using the NUMERIC type to create a supercharged BIGINT

The NUMERIC type (also called DECIMAL, it’s the same thing) is the chad of number types. You can give it an arbitrary amount of precision. 💪

You define it like this: NUMERIC(precision, scale), where scale defines how many digits after the comma you want, and precision specifies the amount of digits before+after you want.

By setting scale to 0 (or omit it), we effectively create an Integer. BIGINT is effectively equal to NUMERIC(19)

The max size uint256 in Ethereum can be represented by NUMERIC(78).

NUMERIC(78) can be overkill. If you are tracking ETH balances, a lower precicion count, like NUMERIC(36), is probably sufficient (can go up to 1.000.000.000.000.000.000 ETH).

What if I want to store it with commas and don’t care about precision?

There might be some valid usecases of storing eth numbers directly in their comma-form, like 5.25 ETH.

Depending on your product requirements, you might not need that much precicion, and you can store the value directly in a REAL or DOUBLE PRECISION data type.

This means you couldn’t store really small values.

In theory, you could also use the NUMERIC type and explicitly set the scale to 18. However, this is a bit dirtier, and you might as well store it as proper ints when you’re at it.

Overview of different float values you could use:

Sometimes, we have the source of truth on-chain, so doing some trade-offs in our db can be a sensible option.

Conclusion

It would have made our lives easier if BIGINT was limitless out of the box, but turns out that NUMERIC(78) gives us exactly what we need. And TEXT is also a great option when we don’t plan to do processing or computation.

Our options are

• NUMERIC(78) — (best option) when you need the full ethereum uint256 range

• NUMERIC(36) — sufficient for most token amounts (up to 1.000.000.000.000.000.000 ETH)

• TEXT — best when you don’t need processing

• As a decimal number in REAL — not recommended, but ok if you are just doing some dirty calculations don’t care too much about data correctness

Go crazy with your ETH numbers in Postgres!