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

Answer 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

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

Answer 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);