Drizzle TeamDT
Drizzle Team•3y ago
saM69420

Creating a Case-Insensitive Unique Index

I want to be able to select a user by their username but have usernames be case insensitive:

const user = await db.select()
  .from(users)
  .where(eq(sql`lower(${usersTable.username})`, sql`lower(${params.username})`))
  .then((result) => result[0]);


If I setup a users table like this:

export const users = pgTable("users", {
  username: varchar("username", { length: 30 })
    .notNull()
    .unique("users_username_unique", { nulls: "not distinct" }),
  //...
});


It will create in an index definition:

CREATE UNIQUE INDEX users_username_unique ON public.users USING btree (username) NULLS NOT DISTINCT;


Is there a way to create a lowercase index:

CREATE UNIQUE INDEX users_username_unique ON public.users USING btree (LOWER(username)) NULLS NOT DISTINCT


Or is there another way to achieve a lowercase equality check on a unique index?
Was this page helpful?