.tip

Storing lower(email) in the application and hoping every writer remembers is a bug waiting for a second writer. The database can 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 added virtual generated columns too, computed on read, for values that are cheap and not worth the storage.