I have the same but when i use the nullsNotDistinct option:

t => [ unique('unique_assignment').on(t.documentId, t.templateId, t.companyId, t.assetId, t.buildingId).nullsNotDistinct(), ]

When i leave out the option the push command doesn't ask to recreate.
Also, i can't use nullsNotDistinct on uniqueIndex

@kaeon Try t => [ unique('unique_assignment').on(t.assetId, t.buildingId, t.companyId, t.documentId, t.templateId ).nullsNotDistinct(), ]

I think what happens is when the db introspection happens, the columns are sorted. This is compared against your current state, which is always out of order.

Not sure of the mechanisms used, but either they both should be sorted, or the order should be deemed to be important and neither should be sorted, any column reordering should result in a dangerous operation.

Trying to be clever and ascertaining in which situations column order definition will not matter seems like a recipe for disaster

@kaeon Try t => [ unique('unique_assignment').on(t.assetId, t.buildingId, t.companyId, t.documentId, t.templateId ).nullsNotDistinct(), ]

thanks for the suggestion but unfortunately result is the same. I also tried to order them in the order of the columns in my schema now but also same result

export const tCompDocumentAssignment = pgTable( 'comp_document_assignment', { id: nanoIdCharPrimary, documentId: nanoIdChar() .references(() => tCompDocument.id) .notNull(), assetId: nanoIdChar().references(() => tCompAsset.id), buildingId: nanoIdChar().references(() => tCompBuilding.id), templateId: nanoIdChar() .references(() => tBcDocument.id) .notNull(), companyId: nanoIdChar() .references(() => tCompany.id) .notNull(), ...commonFields, }, t => [ unique('unique_doc_assignment').on(t.documentId, t.assetId, t.buildingId, t.templateId, t.companyId).nullsNotDistinct(), ] );

Good suggestion!
I just did and I don't see any differences (also not in the order) except there's no nullsNotDistinct
When i remove unique -> push -> add it again, it stays the same

export const compDocumentAssignment = pgTable("comp_document_assignment", {
id: char({ length: 12 }).primaryKey().notNull(),
documentId: char("document_id", { length: 12 }).notNull(),
templateId: char("template_id", { length: 12 }).notNull(),
createdAt: timestamp("created_at", { mode: 'string' }).default(sqlCURRENT_TIMESTAMP).notNull(),
createdBy: char("created_by", { length: 12 }),
updatedAt: timestamp("updated_at", { mode: 'string' }).default(sqlCURRENT_TIMESTAMP).notNull(),
updatedBy: char("updated_by", { length: 12 }),
companyId: char("company_id", { length: 12 }).notNull(),
assetId: char("asset_id", { length: 12 }),
buildingId: char("building_id", { length: 12 }),
}, (table) => [
foreignKey({
columns: [table.documentId],
foreignColumns: [compDocument.id],
name: "comp_document_assignment_document_id_comp_document_id_fk"
}),
foreignKey({
columns: [table.templateId],
foreignColumns: [bcDocument.id],
name: "comp_document_assignment_template_id_bc_document_id_fk"
}),
foreignKey({
columns: [table.companyId],
foreignColumns: [company.id],
name: "comp_document_assignment_company_id_company_id_fk"
}),
foreignKey({
columns: [table.assetId],
foreignColumns: [compAsset.id],
name: "comp_document_assignment_asset_id_comp_asset_id_fk"
}),
foreignKey({
columns: [table.buildingId],
foreignColumns: [compBuilding.id],
name: "comp_document_assignment_building_id_comp_building_id_fk"
}),
unique("unique_doc_assignment").on(table.documentId, table.templateId, table.companyId, table.assetId, table.buildingId),
]);

I put a PR in which appears to fix it. I tested OP's example and it now doesn't try to re-create the index with the columns ordered wrongly.

I put a PR in which appears to fix it. I tested OP's example and it now doesn't try to re-create the index with the columns ordered wrongly.

Hi @Mardoxx Mardoxx, thanks for this PR! Probably it will take a while before implemented. Can you think of a workaround for me to get these unique NullsNotDistinct implemented anyway with the current version. It's blocking my project!