postgres identifier primary key - ghdrako/doc_snipets GitHub Wiki

• https://supabase.com/blog/choosing-a-postgres-primary-key

integer/biginteger

CREATE TABLE users (
  id integer PRIMARY KEY,
  name text NOT NULL
);
INSERT INTO users (id, email, name)
SELECT COUNT(*) + 1, 'new_user' FROM users;

serial/bigserial

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).

Using a serial column to create the users table would look like this:

CREATE TABLE users (
  id serial PRIMARY KEY,
  name text NOT NULL
);

INSERT INTO users_serial (name) VALUES ('new user');

Using a serial column is operationally similar to the following SQL:

CREATE SEQUENCE tablename_colname_seq AS integer;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

When writing automation that simply iterates through id values, note that serial columns can have gaps, even if you never DELETE (e.x. if an INSERT was rolled back — sequences live outside transactions).

serial is PostgreSQL specific (i.e. not SQL standards compliant) but it solved in Postgres 10. Postgres 10 added support for the IDENTITY column syntax in CREATE TABLE

https://www.enterprisedb.com/blog/postgresql-10-identity-columns-explained

CREATE TABLE (
  id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  email citext NOT NULL CHECK (LENGTH(email) < 255),
  name text NOT NULL
)

Random Numeric IDs

from secrets import randbelow
# ...
def create_user():
    """
    Add new user to the database (using secure random numbers)
    """
    user_id = randrange(1, MAX_RANDOM_USER_ID)
    user = User(id=user_id, name="new user")
    db.save(user)

Random UUIDs

There are a lot of versions of UUID, but let's discuss the ones we're more likely to use/see day to day.

• UUIDv1

Version 1 UUIDs have three two components:

a 60 bit date-time (at nanosecond precision)
a 48 bit [MAC address](https://en.wikipedia.org/wiki/MAC_address)

You can generate v1 UUIDs in Postgres natively thanks to the uuid-ossp contrib module. Here's how to generate a v1 UUID with random MAC address:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION

SELECT uuid_generate_v1mc();

          uuid_generate_v1mc
--------------------------------------
 dd1bbf10-0b47-11ed-80de-db48f6faaf86
 
(1 row)