38

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

socksocket
  • 4,271
  • 11
  • 45
  • 70

3 Answers3

51

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

Mark Loiseau
  • 764
  • 7
  • 6
  • 5
    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. – Peter Knego Nov 21 '20 at 19:38
  • 2
    if the security of the data depends on the datatype of PK then we have a bigger problem. @PeterKnego – Rohit Naik Jan 02 '23 at 11:08
  • @RohitNaik sometimes you have public data, but you still do not want to allow scraping all data on your site. For example images or public user profiles. Also, my recommendation is not about datatype, but against serially generating identifiers in a way that can be easily discovered. – Peter Knego Jan 03 '23 at 07:09
7

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.

  • ... and probably some processing power too, at least in 32-bits systems. – Rodrigo Sep 02 '15 at 20:30
  • 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 – jave.web May 23 '21 at 18:23
2

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);
Vivek Seth
  • 177
  • 1
  • 6