7 replies

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]);

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" }),
  //...
});

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;

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

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?

Drizzle Team•3y ago•

7 replies

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]);

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" }),
  //...
});

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;

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

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?

Creating a Case-Insensitive Unique Index

Similar Threads

Creating a Case-Insensitive Unique Index

Similar Threads

Similar Threads

Similar Threads