How to set opclass for index with custom sql column

we do have following definitions, after we used

drizzle-kit pull

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

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')

.op('text_ops')

, but that doesn't work for the custom index column with

sql

sql

Drizzle Team•15mo ago

alex (he/him)

How to set opclass for index with custom sql column

we do have following definitions, after we used

drizzle-kit pull

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

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')

.op('text_ops')

, but that doesn't work for the custom index column with

sql

sql

How to set opclass for index with custom sql column

Similar Threads

How to set opclass for index with custom sql column

Similar Threads

Similar Threads

Similar Threads