PART 1 · DATA TYPES AND KEYS
Surrogate Keys and Choosing Physical Types in PostgreSQL
Four column-level solutions that eliminate common bugs in production
1. GENERATED ALWAYS AS IDENTITY instead of serial
2. timestamptz instead of timestamp
• The old serial function allows you to accidentally insert an explicit ID (INSERT ... VALUES (5)), after which the sequence fails due to a duplicate key.
• GENERATED ALWAYS AS IDENTITY — SQL standard: the database generates the ID itself and blocks manual insertion without OVERRIDING SYSTEM VALUE.
• `timestamp without time zone` does not take the time zone into account—when a container is moved, the event times shift.
• `timestamptz` stores the absolute time in UTC and correctly compares match and purchase events across any servers.
3. Use integer or numeric instead of float to save resources
4. varchar(N) + CHECK for codes and statuses
• Floating-point numbers (real, double precision) result in binary fraction rounding errors (0.1 + 0.2 ≠ 0.3).
• We store in-game currency and prices only as integers (integer / bigint) or as the precise decimal type numeric(12,2).
• In PostgreSQL, `varchar(32)` prevents a megabyte-long string from being accidentally entered into the `nickname` or `clan_tag` fields.
• For enumerated statuses (rarity, mode), the combination of `varchar(16)` and `CHECK (rarity IN (...))` is easier to migrate than an `ENUM`.
Remember: always specify constraints explicitly (CONSTRAINT chk_...)—that way, if the backend crashes with an error, the log will contain a clear name for the rule.