Skip to content

.tip

· one of the small things

Let a generated column hold the derived value

Storing lower(email) from the application and hoping every writer remembers is a bug waiting for the second writer to show up. Let the database own it.

ALTER TABLE users ADD COLUMN email_lower text
  GENERATED ALWAYS AS (lower(email)) STORED;
CREATE UNIQUE INDEX users_email_lower ON users (email_lower);

Postgres 18 also added virtual generated columns, computed on read, for values that are cheap to work out and not worth the storage.

postgresqlschema-design