Support unique NULLS NOT DISTINCT in console

[deathemperor]

Is your proposal related to a problem?

Use soft delete with deleted_at, then create an unique constraints with deleted_at as one of the columns: when deleted_at = null (not deleted yet), it loses the unique constraint

Describe the solution you'd like

add an option to set unique NULLS NOT DISTINCT

Describe alternatives you've considered

Use SQL to write queries instead of using the UI

[jdinartejesus]

@deathemperor did you find a solution for this? I'm having the same issue.

[deathemperor]

@jdinartejesus I write these queries myself

[jdinartejesus]

@jdinartejesus I write these queries myself

I tried but still didn't work, did you create using index?

CREATE UNIQUE INDEX idx_unique_active_rows
ON match_lineup (match_id, player_id, team_id)
WHERE deleted_at IS NULL;

Issue I had is now I can't use upsert/insert using idx_unique_active_rows as constraint.

[deathemperor]

@jdinartejesus try below

alter table "public"."plan_insured_benefits" add constraint "plan_insured_benefits_plan_id_insured_benefit_id_deleted_at_key" unique nulls not distinct ("plan_id", "insured_benefit_id", "deleted_at");

requires postgresql 16+