# 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.

my_token_table | ㅤ | ㅤ | ㅤ |

value ( `BIGINT` ) | decimals ( `INT` ) | notes | does it work? |

2500000 | 6 | 2.5 USDC | ✅ |

10500000000000000000 | 18 | 10.5 ETH | ❌ TOO BIG FOR POSTGRES ❌ |

## Option 1: Store numbers as a strings

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

my_token_table | ㅤ | ㅤ |

value ( `TEXT` ) | decimals ( `INT` ) | notes |

“2500000” | 6 | 2.5 USDC |

“1050000000000000000” | 18 | 10.5 ETH |

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)`

.

my_token_table | ㅤ | ㅤ |

value ( `NUMERIC(78)` ) | decimals ( `INT` ) | notes |

2500000 | 6 | 2.5 USDC |

10500000000000000000 | 18 | 10.5 ETH |

`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.

my_token_table | ㅤ | ㅤ |

value ( `REAL` ) | notes | does it work? |

2.5 | USDC (6 decimals) | ✅ |

10.5 | ETH (18 decimals) | ✅ |

ETH (18 decimals) | ❌ Value is rounded down to 0 |

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:

Data type | Decimal places of precision | Minimum precision to store safely | Maximum number |

`REAL` | 6 | 0.000001 ETH | ㅤ |

`DOUBLE PRECISION` | 15 | 0.000000000000001 ETH | ㅤ |

`NUMERIC(24, 18)` | 18 | 0.000000000000000001 ETH (1 wei) | 1.000.000 ETH |

`NUMERIC(30, 18)` | 18 | 0.000000000000000001 ETH (1 wei) | 1.000.000.000.000 ETH |

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!