How do I store an unsigned int in postgres sql?

Question

How do I store an unsigned int (uint32) in postgres? I noticed that numeric(10,0) would fit the number of digits, but is this the best way?

On further research another similar problem is storing a uint64. I've found numeric(20,0) check (BETWEEN 0 AND '18446744073709551615'::numeric(20,0)). There aren't any native types for this I believe.

Have a look at [Why unsigned integer is not available in PostgreSQL?](https://stackoverflow.com/q/20810134/1048572) — Bergi, Sep 21 '19 at 14:56

Mitch Wheat · Accepted Answer · 2014-09-09T00:25:44.973

8

Arithmetic on numeric values is very slow compared to the integer types.

Use bigint. It stores an 8 byte integer up to 2^63 - 1 = 9223372036854775807

[You probably don't need the entire unsigned range]

edited Sep 09 '14 at 00:25

answered Sep 09 '14 at 00:17

Mitch Wheat

295,962
43
465
541

1

I want to store the exact unsigned value.. So bigint is faster than numeric(10,0) check (value >= 0 and value < '4294967296'::numeric(10,0))? – Fire Sep 09 '14 at 00:24
3

@Fire Much, much faster. You still need the check constraint. – Craig Ringer Sep 09 '14 at 00:25
So `bigint check (value >= 0 and value < '4294967296'::bigint)` – Fire Sep 09 '14 at 00:31
9

What if you're trying to store an unsigned Int64... and actually need the whole unsigned range? – Douglas Gaskell Feb 04 '22 at 18:01

How do I store an unsigned int in postgres sql?

1 Answers1