Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I store an unsigned int in postgres sql?

Tags:

postgresql

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.

like image 334
Fire Avatar asked Sep 09 '14 00:09

Fire


People also ask

How is unsigned int stored?

The unsigned int can contain storage size either 2 or 4 bytes where values ranging from [0 to 65,535] or [0 to 4,294,967,295]. The format specifier used for an unsigned int data type in C is “ %u ”.

Does Postgres support unsigned?

PostgreSQL doesn't support the UNSIGNED attribute, but it an be enforced by using the CHECK constraint. Otherwise, negative values could be inserted in pgsql databases.

How do I use unsigned int?

Unsigned integers are used when we know that the value that we are storing will always be non-negative (zero or positive). Note: it is almost always the case that you could use a regular integer variable in place of an unsigned integer.


1 Answers

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]

like image 183
Mitch Wheat Avatar answered Sep 25 '22 20:09

Mitch Wheat