By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users (id TEXT, email VARCHAR(255));
CREATE UNIQUE INDEX auth0_users_email_idx
ON users ((CASE WHEN id NOT LIKE 'auth0|%' THEN email END));
Records: 0 Duplicates: 0 Warnings: 0
INSERT INTO users VALUES ('1', 'email1@dummy.com');
INSERT INTO users VALUES ('2', 'email2@dummy.com');
INSERT INTO users VALUES ('auth0|email1', 'email1@dummy.com');
INSERT INTO users VALUES ('auth0|email1', 'email3@dummy.com');
INSERT INTO users VALUES ('3', 'email1@dummy.com');
Duplicate entry 'email1@dummy.com' for key 'users.auth0_users_email_idx'
INSERT INTO users VALUES ('auth0|email2', 'email1@dummy.com');
DROP TABLE users;
CREATE TABLE users (id TEXT, email TEXT);
CREATE UNIQUE INDEX auth0_users_email_idx
ON users ((CASE WHEN id NOT LIKE 'auth0|%' THEN email END));
Cannot create a functional index on an expression that returns a BLOB or TEXT. Please consider using CAST.