PostgreSQL check constraints are not updated when definition changes

[macdonsp]

When doing some testing, I discovered that if you update the definition of a check constraint when using PostgreSQL with HCL input, the change is not detected or applied. To reproduce, start with an initial schema:

schema "public" {
}
table "categories" {
  schema = schema.public
  column "id" {
    null = false
    type = int
  }
  column "name" {
    null = true
    type = varchar(100)
  }
  primary_key {
    columns = [column.id]
  }
}

Run a schema apply which creates the table:

$ atlas schema apply -u "postgresql://localhost:5432/testing?sslmode=disable" --dev-url "docker://postgres/14" -f schema.hcl --auto-approve
-- Planned Changes:
-- Create "categories" table
CREATE TABLE "public"."categories" ("id" integer NOT NULL, "name" character varying(100) NULL, PRIMARY KEY ("id"));

Add a constraint to the table definition:

schema "public" {
}
table "categories" {
  schema = schema.public
  column "id" {
    null = false
    type = int
  }
  column "name" {
    null = true
    type = varchar(100)
  }
  check "validate_id" {
    expr = "id >= 100"
  }
  primary_key {
    columns = [column.id]
  }
}

Run a schema apply which creates the constraint:

$ atlas schema apply -u "postgresql://localhost:5432/testing?sslmode=disable" --dev-url "docker://postgres/14" -f schema.hcl --auto-approve
-- Planned Changes:
-- Modify "categories" table
ALTER TABLE "public"."categories" ADD CONSTRAINT "validate_id" CHECK (id >= 100);

Modify the constraint definition in the schema, changing "id >= 100" to "id >= 500":

schema "public" {
}
table "categories" {
  schema = schema.public
  column "id" {
    null = false
    type = int
  }
  column "name" {
    null = true
    type = varchar(100)
  }
  check "validate_id" {
    expr = "id >= 500"
  }
  primary_key {
    columns = [column.id]
  }
}

Run a schema apply, which does not detect or apply the change:

$ atlas schema apply -u "postgresql://localhost:5432/testing?sslmode=disable" --dev-url "docker://postgres/14" -f schema.hcl --auto-approve
Schema is synced, no changes to be made

This was discovered using the latest version of Atlas. Please let me know if you would like any additional information.

Thanks!

[alexcwatt]

I found something similar with SQLite. I had a constraint like this

check "type" {
  expr = "type IN ('meeting', 'phone_call')"
}

and found that if I added more options to the constraint, it would not update the database.

[jpjoux]

Same issue as described
I have to delete the constraint, apply the modification and create a new one ....
When can it be corrected ?

[a8m]

Thanks for reporting this. Issue was resolved with #3208