3 replies

Use COALESCE in index

How can I generate this sql:

CREATE UNIQUE INDEX "IndexName" ON "Table" ("foo", COALESCE ("bar", ''));

CREATE UNIQUE INDEX "IndexName" ON "Table" ("foo", COALESCE ("bar", ''));

When I use the following schema definition:

uniqueIndex("IndexName").on(table.foo, sql`COALESCE(${table.bar}, '')`)

uniqueIndex("IndexName").on(table.foo, sql`COALESCE(${table.bar}, '')`)

The comma in COALESCE get escaped:

CREATE UNIQUE INDEX `IndexName` ON `Table` (`foo`,`COALESCE("bar"`,` '')`);

CREATE UNIQUE INDEX `IndexName` ON `Table` (`foo`,`COALESCE("bar"`,` '')`);