K
Kysely•3d ago
Tim Lonsdale

Can't figure out how to add a unique index on (foo, LOWER(bar)) for a table

Sorry chaps if this is obvious but can't seem to see any mention of this in the docs. I need to create a unique index on a tuple of columns - with the caveat that the second column is "case insensitive" - i achieve this by lowercasing the second column. I believe this is quite normal to do in vanilla pg but am unsure what the idiomatic way to do this in kysely is...
Solution:
Answer: When constructing an index, you can daisy chain .column and .expression. Thus, you would do: ```typescript...
Jump to solution
5 Replies
Tim Lonsdale
Tim LonsdaleOP•3d ago
And then as an addendum - how do I "on conflict (foo, LOWER(bar))"
Solution
Tim Lonsdale
Tim Lonsdale•3d ago
Answer: When constructing an index, you can daisy chain .column and .expression. Thus, you would do: 
await datbase.schema
  .createIndex("ix")
  .on("table")
  .column("foo")
  .expression(sql`LOWER(${sql.ref("bar")})`)
  .unique()
await datbase.schema
  .createIndex("ix")
  .on("table")
  .column("foo")
  .expression(sql`LOWER(${sql.ref("bar")})`)
  .unique()
That would compile to: 
CREATE UNIQUE INDEX ix ON "table" ("foo", LOWER("bar"));
CREATE UNIQUE INDEX ix ON "table" ("foo", LOWER("bar"));
Tim Lonsdale
Tim LonsdaleOP•3d ago
@Igal - I actually think this is not fully correct. It works w.r.t. creating indices, but not specifying on conflict constraints. If I do a daisy chain it only picks up the first "column" reference and ignores the expression. Is this a bug?
Tim Lonsdale
Tim LonsdaleOP•3d ago
from the kysely playground. LMK your thoughts
No description
Tim Lonsdale
Tim LonsdaleOP•3d ago
Shall I create an issue on the github page? As a workaround, one could do: 
  database
    .insertInto("table")
    .values({})
    .onConflict(oc => oc
      .expression(sql`${sql.ref("foo")},LOWER(${sql.ref("bar")})`)
    )
  database
    .insertInto("table")
    .values({})
    .onConflict(oc => oc
      .expression(sql`${sql.ref("foo")},LOWER(${sql.ref("bar")})`)
    )

Did you find this page helpful?