postgres identifier primary key uuid - 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).

IDENTITY column

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
)

CREATE TABLE sale (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sold_at TIMESTAMPTZ,
    amount INT
);

Instead of using GENERATED BY DEFAULT, use GENERATED ALWAYS. It prevent to accidently set fixed value to identity column. It cause problem if the same value next was generated automaticali - duplicate key.

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)

db=# SELECT uuid_generate_v4() AS uuid;
                 uuid
──────────────────────────────────────
 8e55146d-0ce5-40ab-a346-5dbd466ff5f2

Starting at version 13 there is a built-in function to generate random (version 4) UUIDs:

db=# SELECT gen_random_uuid() AS uuid;
                 uuid
──────────────────────────────────────
 ba1ac0f5-5d4d-4d80-974d-521dbdcca2b2

The uuid-ossp extension is still needed if you want to generate UUIDs other than version 4.