Repository: ariga/atlas
Pull request #3021 (2024-08-04 15:48:08)

sql/postgres: fix nulls not distinct usage in unique constraints
Fixed https://github.com/ariga/atlas/issues/2818


Patch:
diff --git a/sql/postgres/migrate.go b/sql/postgres/migrate.go
index bc051914..4d302f8f 100644
--- a/sql/postgres/migrate.go
+++ b/sql/postgres/migrate.go
@@ -1151,9 +1151,9 @@ func (s *state) index(b *sqlx.Builder, idx *schema.Index) error {
 			})
 		})
 	}
-	// Avoid appending the default behavior, which NULL values are distinct.
-	if n := (IndexNullsDistinct{}); sqlx.Has(idx.Attrs, &n) && !n.V {
-		b.P("NULLS NOT DISTINCT")
+	// Handled separately by the UNIQUE builder.
+	if _, ok := uniqueConst(idx.Attrs); !ok {
+		nullsNotDistinct(b, idx)
 	}
 	if p, ok := indexStorageParams(idx.Attrs); ok {
 		b.P("WITH")
@@ -1228,6 +1228,9 @@ func (s *state) unique(b *sqlx.Builder, idx *schema.Index) error {
 		name = idx.Name
 	}
 	b.P("CONSTRAINT").Ident(name).P("UNIQUE")
+	// In UNIQUE constraints, the NULLS [NOT] DISTINCT
+	// clause is written before the index parts.
+	nullsNotDistinct(b, idx)
 	return s.index(b, idx)
 }
 
@@ -1426,3 +1429,11 @@ func dropConst(c schema.Change) bool {
 		return false
 	}
 }
+
+// nullsNotDistinct handles the NULLS [NOT] DISTINCT clause for indexes.
+func nullsNotDistinct(b *sqlx.Builder, idx *schema.Index) {
+	// Avoid appending the default behavior, which NULL values are distinct.
+	if n := (IndexNullsDistinct{}); sqlx.Has(idx.Attrs, &n) && !n.V {
+		b.P("NULLS NOT DISTINCT")
+	}
+}


Test patch:
diff --git a/internal/integration/testdata/postgres/index-nulls-distinct.txt b/internal/integration/testdata/postgres/index-nulls-distinct.txt
index cc50801b..e0436fb7 100644
--- a/internal/integration/testdata/postgres/index-nulls-distinct.txt
+++ b/internal/integration/testdata/postgres/index-nulls-distinct.txt
@@ -22,6 +22,10 @@ table "users" {
     columns        = [column.c]
     nulls_distinct = false
   }
+  unique "nulls_not_distinct2" {
+    columns        = [column.c]
+    nulls_distinct = false
+  }
 }
 
 -- 1.inspect.hcl --
@@ -36,6 +40,10 @@ table "users" {
     columns        = [column.c]
     nulls_distinct = false
   }
+  unique "nulls_not_distinct2" {
+    columns        = [column.c]
+    nulls_distinct = false
+  }
 }
 schema "script_index_nulls_distinct" {
 }
@@ -46,6 +54,7 @@ schema "script_index_nulls_distinct" {
  c      | integer |           | not null |
 Indexes:
     "nulls_not_distinct" UNIQUE, btree (c) NULLS NOT DISTINCT
+    "nulls_not_distinct2" UNIQUE CONSTRAINT, btree (c) NULLS NOT DISTINCT
 
 -- 2.hcl --
 schema "script_index_nulls_distinct" {}
diff --git a/sql/postgres/migrate_test.go b/sql/postgres/migrate_test.go
index 3cde8a36..a74635fb 100644
--- a/sql/postgres/migrate_test.go
+++ b/sql/postgres/migrate_test.go
@@ -811,6 +811,19 @@ func TestPlanChanges(t *testing.T) {
 									},
 								},
 							},
+							&schema.AddIndex{
+								I: &schema.Index{
+									Unique: true,
+									Name:   "unique_const_nulls_not_distinct",
+									Parts: []*schema.IndexPart{
+										{C: users.Columns[0]},
+									},
+									Attrs: []schema.Attr{
+										&IndexNullsDistinct{V: false},
+										&Constraint{N: "unique_const", T: "u"},
+									},
+								},
+							},
 						},
 					}
 				}(),
@@ -824,8 +837,8 @@ func TestPlanChanges(t *testing.T) {
 						Reverse: `CREATE INDEX CONCURRENTLY "drop_con" ON "users" ("id")`,
 					},
 					{
-						Cmd:     `ALTER TABLE "users" DROP CONSTRAINT "id_nonzero", ADD COLUMN "name" character varying(255) NOT NULL DEFAULT 'logged_in', ADD COLUMN "last" character varying(255) NOT NULL DEFAULT 'logged_in', ADD CONSTRAINT "name_not_empty" CHECK ("name" <> ''), ADD CONSTRAINT "positive_id" CHECK ("id" > 0) NOT VALID, DROP CONSTRAINT "id_iseven", ADD CONSTRAINT "id_iseven" CHECK (("id") % 2 = 0)`,
-						Reverse: `ALTER TABLE "users" DROP CONSTRAINT "id_iseven", ADD CONSTRAINT "id_iseven" CHECK ("id" % 2 = 0), DROP CONSTRAINT "positive_id", DROP CONSTRAINT "name_not_empty", DROP COLUMN "last", DROP COLUMN "name", ADD CONSTRAINT "id_nonzero" CHECK ("id" <> 0)`,
+						Cmd:     `ALTER TABLE "users" DROP CONSTRAINT "id_nonzero", ADD COLUMN "name" character varying(255) NOT NULL DEFAULT 'logged_in', ADD COLUMN "last" character varying(255) NOT NULL DEFAULT 'logged_in', ADD CONSTRAINT "name_not_empty" CHECK ("name" <> ''), ADD CONSTRAINT "positive_id" CHECK ("id" > 0) NOT VALID, DROP CONSTRAINT "id_iseven", ADD CONSTRAINT "id_iseven" CHECK (("id") % 2 = 0), ADD CONSTRAINT "unique_const" UNIQUE NULLS NOT DISTINCT ("id")`,
+						Reverse: `ALTER TABLE "users" DROP CONSTRAINT "unique_const_nulls_not_distinct", DROP CONSTRAINT "id_iseven", ADD CONSTRAINT "id_iseven" CHECK ("id" % 2 = 0), DROP CONSTRAINT "positive_id", DROP CONSTRAINT "name_not_empty", DROP COLUMN "last", DROP COLUMN "name", ADD CONSTRAINT "id_nonzero" CHECK ("id" <> 0)`,
 					},
 					{
 						Cmd:     `CREATE INDEX "id_key" ON "users" ("id" DESC) WHERE success`,