Tim Lonsdale Comments - Answer Overflow

Tim Lonsdale

•Created by Tim Lonsdale on 4/17/2025 in #help

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

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")})`)
    )

9 replies

KKysely

•Created by Tim Lonsdale on 4/17/2025 in #help

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

Shall I create an issue on the github page?

9 replies

KKysely

•Created by Tim Lonsdale on 4/17/2025 in #help

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

9 replies

KKysely

•Created by Tim Lonsdale on 4/17/2025 in #help

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

@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?

9 replies

KKysely

•Created by Tim Lonsdale on 4/17/2025 in #help

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

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

9 replies

KKysely

•Created by Tim Lonsdale on 4/17/2025 in #help

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

And then as an addendum - how do I "on conflict (foo, LOWER(bar))"

9 replies

KKysely

•Created by Tim Lonsdale on 2/11/2025 in #help

Getting the underlying connection pool/client from a kysely wrapper

Thanks mate

4 replies

KKysely

•Created by Tim Lonsdale on 1/27/2025 in #help

Is it okay to repeatedly wrap a PG connection pool with kysely?

nice one - cheers mate

5 replies

KKysely

•Created by Tim Lonsdale on 1/27/2025 in #help

Is it okay to repeatedly wrap a PG connection pool with kysely?

to clarify - I understand kysely to simply be a wrapper that interfaces with the underlying pool passed in and is essentially "stateless"

5 replies

KKysely

•Created by Tim Lonsdale on 1/17/2025 in #help

Overcoming issue with `withSchema` and `forUpdate`

Thanks for this @koskimas!

5 replies

KKysely

•Created by Tim Lonsdale on 12/7/2024 in #help

Unioning an array of sql`{SQL_QUERY}`

thanks mate!

5 replies

KKysely

•Created by Tim Lonsdale on 12/7/2024 in #help

Unioning an array of sql`{SQL_QUERY}`

I suppose that would work

5 replies

KKysely

•Created by Tim Lonsdale on 12/7/2024 in #help

Unioning an array of sql`{SQL_QUERY}`

do I use Array.reduce?

5 replies

Gaming

Programming