PostgreSQL Best Practices

Chapter 2 · Practice 3 of 4

Enforce uniqueness with constraints, not application checks

From Constraints that protect your data

Why it matters

"Check if the email exists, then insert" has a race: two requests can both pass the check and both insert. Under load, duplicate accounts appear. Only a unique constraint or unique index closes that gap.

Pick the form that matches the rule:

  • Case-insensitive uniqueness: index an expression such as lower(email).
  • Unique among active rows: use a partial unique index.
  • Upserts: use INSERT ... ON CONFLICT.

Then handle the unique violation error, SQLSTATE 23505, in your code and turn it into a friendly message.

ts
if (err.code === '23505') return { error: 'That email is already registered' };

Example

SQL
CREATE UNIQUE INDEX users_email_lower_key ON users (lower(email));

INSERT INTO users (email) VALUES ('a@example.com')
ON CONFLICT (lower(email)) DO NOTHING;