How to set opclass for index with custom sql column

we do have following definitions, after we used drizzle-kit pull

uniqueIndex("sap_id_upper_trim_idx").using("btree", sql`upper(TRIM(BOTH FROM sap_id))`),

uniqueIndex("sap_id_upper_trim_idx").using("btree", sql`upper(TRIM(BOTH FROM sap_id))`),

Now the issue is that, drizzle-kit generate thinks it needs to drop the index and recreates it, because it doesn't understand that the opclass is optional. This is the initial state from introspection

CREATE UNIQUE INDEX "sap_id_upper_trim_idx" ON "companies" USING btree (upper(TRIM(BOTH FROM sap_id)) text_ops);--> statement-breakpoint

CREATE UNIQUE INDEX "sap_id_upper_trim_idx" ON "companies" USING btree (upper(TRIM(BOTH FROM sap_id)) text_ops);--> statement-breakpoint

This is what drizzle generates as a migration

DROP INDEX "sap_id_upper_trim_idx";--> statement-breakpoint
CREATE UNIQUE INDEX "sap_id_upper_trim_idx" ON "companies" USING btree (upper(TRIM(BOTH FROM sap_id)));--> statement-breakpoint

DROP INDEX "sap_id_upper_trim_idx";--> statement-breakpoint
CREATE UNIQUE INDEX "sap_id_upper_trim_idx" ON "companies" USING btree (upper(TRIM(BOTH FROM sap_id)));--> statement-breakpoint

I know for other columns we can use .op('text_ops'), but that doesn't work for the custom index column with sql?

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

How to set opclass for index with custom sql column