CODEWITHSUNDEEP
postgresqldatabase-designdata-warehousesqldatatypes
socksocket
socksocket

Reputation: 4371

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

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

Upvotes: 42

Views: 44007

Answers (3)

Vivek Seth
Vivek Seth

Reputation: 387

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

Upvotes: 13

user330315
user330315

Reputation:

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.

Upvotes: 8

Mark Loiseau
Mark Loiseau

Reputation: 812

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

Upvotes: 55

Related Questions