what is the right data type for unique key in postgresql DB?

Question

which data type should I choose for a unique key (id of a user for example) in postgresql database's table?
does bigint is the one?

thanks

Solution 1

Use the serial type for automatically incrementing unique ids.

If you plan to have more than two billion entries, use bigserial. serial is the PostgresSQL equivalent of MySQL's AUTO_INCREMENT.

PostgresSQL Documentation: Numeric Types

Solution 2

bigint (or bigserial if you need auto-incrementing keys) is just fine.

If know for certain that you are not going to load too many rows, you might consider integer (or a regular serial) and potentially save some harddisk space.

Solution 3

According to this answer the current recommended approach to doing auto-increment unique IDs is to use the generated as identity syntax instead of serial.

Here's an example:

-- the old way
create table t1 (id serial primary key);

-- the new way
create table t2 (id integer primary key generated always as identity);

Comments

[Rodrigo]

... and probably some processing power too, at least in 32-bits systems.

[Peter Knego]

I'd recommend against it: if you use serial and expose this ID to public via API, then you just made life easy for anyone trying to scrape data from your site.

[jave.web]

If you plan to have a lot of data there or you may have - use the largest type available. It does happen on real systems/apps sometimes that basic int is exhausted