Drizzle TeamDT
Drizzle Team•2w ago
Michael Schaufelberger

How to specify a composite constraint/index with `nulls not distinct` and a function for a col?

Hi 👋

What I would like to do is something like:
unique()
  .on(sql`LOWER(label)`, table.owner, table.parentGroupId)
  .nullsNotDistinct(),


But then the column lower(label) is just completely omitted by drizzle-kit push:
query error: zero-length delimited identifier at or near """"

ALTER TABLE "groups" ADD CONSTRAINT "groups_unique_idx" UNIQUE NULLS NOT DISTINCT("","owner","parentGroupId");


So I thought to do just an index instead:
uniqueIndex('groups_unique_idx_index_no_nulls').on(
    sql`LOWER(label)`,
    table.owner,
    table.parentGroupId,
  ).nullsNotDistinct() // <-- this is not available

but there nulls not distinct is not available

So my current workaround is:

uniqueIndex('groups_unique_idx_working').using(
    'btree',
    sql`LOWER(label)`,
    table.owner,
    sql`"parentGroupId") NULLS NOT DISTINCT; --`,
  ),

which is basically me tricking drizzle kit by sql injecting the correct syntax.


Note: I'm currently on beta with the legacy relations. But afaik defining indexes and constraints hasn't changed.
Was this page helpful?