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"`,` '')`);

3 Replies

Mykhailo•5mo ago

Hey @mutex! Try to wrap into parentheses:

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

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

mutexOP•5mo ago

Thank you, that did not change anything, I can see from the json snapshot file that it is correct, so this seems like a bug in the generator in drizzle-kit

Mykhailo•5mo ago

got it, I will reproduce and make issue for this case. thank you!

Gaming

Programming

Use COALESCE in index