Generated ID Values - jbrucker/home-log GitHub Wiki

For generating values of an ID field there are a few options:

• GENERATED AS IDENTITY provides a sequence by default but allows manual insertions, which can lead to gaps in the sequence.
• GENERATED BY DEFAULT AS IDENTITY also allows for manual insertions.
• GENERATED ALWAYS AS IDENTITY database always generates the value. Enforces strict sequential order and avoids duplicates. It is still possible to override this using:

  INSERT INTO users (user_id, username) 
  OVERRIDING SYSTEM VALUE 
  VALUES (100, 'Alice');   -- manually specify user_id 100
  

• UUID, UUIDv7, ULID globally unique 16-byte identifiers. Good for distributed applications or where values may be added offline, distributed applications, or where sharding is used.

To specify starting value or increment for GENERATED values use:

  user_id     INTEGER GENERATED AS IDENTITY (
                  START WITH 1000
                  INCREMENT BY 2
                  MAXVALUE 9999
              ) PRIMARY KEY,