From 058082ff6297f9ccdc4977e65ef024e9a093426e Mon Sep 17 00:00:00 2001
From: Ellis Valentiner <ellisvalentiner@gmail.com>
Date: Wed, 20 Dec 2023 09:34:40 -0500
Subject: [PATCH] reverse constraint option ordering

---
 lib/sqlalchemy/dialects/postgresql/base.py |  5 +++--
 test/dialect/postgresql/test_reflection.py | 12 +++++++++---
 2 files changed, 12 insertions(+), 5 deletions(-)

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index f692377dba..6216ad9801 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -4697,9 +4697,10 @@ class PGDialect(default.DefaultDialect):
             # "CHECK (some_boolean_function(a))"
             # "CHECK (((a\n < 1)\n OR\n (a\n >= 5))\n)"
             # "CHECK (a NOT NULL) NO INHERIT"
+            # "CHECK (a NOT NULL) NO INHERIT NOT VALID"
 
             m = re.match(
-                r"^CHECK *\((.+)\)( NOT VALID)?( NO INHERIT)?$", src, flags=re.DOTALL
+                r"^CHECK *\((.+)\)( NO INHERIT)?( NOT VALID)?$", src, flags=re.DOTALL
             )
             if not m:
                 util.warn("Could not parse CHECK constraint text: %r" % src)
@@ -4713,7 +4714,7 @@ class PGDialect(default.DefaultDialect):
                 "sqltext": sqltext,
                 "comment": comment,
             }
-            if m and m.group(2):
+            if m and " NOT VALID" in m.groups():
                 entry["dialect_options"] = {"not_valid": True}
 
             check_constraints[(schema, table_name)].append(entry)
diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py
index 615b51e518..4bb635c1c2 100644
--- a/test/dialect/postgresql/test_reflection.py
+++ b/test/dialect/postgresql/test_reflection.py
@@ -2199,7 +2199,8 @@ class ReflectionTest(
 
     def test_reflect_with_no_inherit_check_constraint(self):
         rows = [
-            ("foo", "some name", "CHECK ((a IS NOT NULL)) NO INHERIT", None)
+            ("foo", "some name", "CHECK ((a IS NOT NULL)) NO INHERIT", None),
+            ("foo", "some name", "CHECK ((a IS NOT NULL)) NO INHERIT NOT VALID", None),
         ]
         conn = mock.Mock(
             execute=lambda *arg, **kw: mock.MagicMock(
@@ -2215,9 +2216,14 @@ class ReflectionTest(
                 {
                     "name": "some name",
                     "sqltext": "a IS NOT NULL",
-                    "dialect_options": {"no_inherit": True},
                     "comment": None,
-                }
+                },
+                {
+                    "name": "some name",
+                    "sqltext": "a IS NOT NULL",
+                    "dialect_options": {"not_valid": True},
+                    "comment": None,
+                },
             ],
         )
 
-- 
2.47.3