From 3fe8d618e3614edc8f9e4241e7521a984f2f21c6 Mon Sep 17 00:00:00 2001
From: Federico Caselli <cfederico87@gmail.com>
Date: Thu, 25 Mar 2021 21:08:21 +0100
Subject: [PATCH] Fixed reflection of identity columns in tables with mixed
 case names in PostgreSQL.

Fixes: #6129
Change-Id: I50480cec03fcb44a668c9b0f9c72950803b771d9
---
 doc/build/changelog/unreleased_14/6129.rst |  6 +++
 lib/sqlalchemy/dialects/postgresql/base.py |  7 ++--
 test/dialect/postgresql/test_reflection.py | 46 ++++++++++++----------
 3 files changed, 34 insertions(+), 25 deletions(-)
 create mode 100644 doc/build/changelog/unreleased_14/6129.rst

diff --git a/doc/build/changelog/unreleased_14/6129.rst b/doc/build/changelog/unreleased_14/6129.rst
new file mode 100644
index 0000000000..458f4a652d
--- /dev/null
+++ b/doc/build/changelog/unreleased_14/6129.rst
@@ -0,0 +1,6 @@
+.. change::
+    :tags: bug, reflection, postgresql
+    :tickets: 6129
+
+    Fixed reflection of identity columns in tables with mixed case names
+    in PostgreSQL.
\ No newline at end of file
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index d52cc2ae6f..6e6f5513d0 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -3582,12 +3582,11 @@ class PGDialect(default.DefaultDialect):
                     'cycle', s.seqcycle)
                 FROM pg_catalog.pg_sequence s
                 JOIN pg_catalog.pg_class c on s.seqrelid = c."oid"
-                JOIN pg_catalog.pg_namespace n on n.oid = c.relnamespace
                 WHERE c.relkind = 'S'
                 AND a.attidentity != ''
-                AND n.nspname || '.' || c.relname =
-                pg_catalog.pg_get_serial_sequence(
-                    a.attrelid::regclass::text, a.attname)
+                AND s.seqrelid = pg_catalog.pg_get_serial_sequence(
+                    a.attrelid::regclass::text, a.attname
+                )::regclass::oid
                 ) as identity_options\
                 """
         else:
diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py
index 6586a8308d..8554649164 100644
--- a/test/dialect/postgresql/test_reflection.py
+++ b/test/dialect/postgresql/test_reflection.py
@@ -1811,30 +1811,34 @@ class IdentityReflectionTest(fixtures.TablesTest):
     __backend__ = True
     __requires__ = ("identity_columns",)
 
+    _names = ("t1", "T2", "MiXeDCaSe!")
+
     @classmethod
     def define_tables(cls, metadata):
-        Table(
-            "t1",
-            metadata,
-            Column(
-                "id1",
-                Integer,
-                Identity(
-                    always=True,
-                    start=2,
-                    increment=3,
-                    minvalue=-2,
-                    maxvalue=42,
-                    cycle=True,
-                    cache=4,
+        for name in cls._names:
+            Table(
+                name,
+                metadata,
+                Column(
+                    "id1",
+                    Integer,
+                    Identity(
+                        always=True,
+                        start=2,
+                        increment=3,
+                        minvalue=-2,
+                        maxvalue=42,
+                        cycle=True,
+                        cache=4,
+                    ),
                 ),
-            ),
-            Column("id2", Integer, Identity()),
-            Column("id3", BigInteger, Identity()),
-            Column("id4", SmallInteger, Identity()),
-        )
+                Column("id2", Integer, Identity()),
+                Column("id3", BigInteger, Identity()),
+                Column("id4", SmallInteger, Identity()),
+            )
 
-    def test_reflect_identity(self, connection):
+    @testing.combinations(*_names, argnames="name")
+    def test_reflect_identity(self, connection, name):
         insp = inspect(connection)
         default = dict(
             always=False,
@@ -1844,7 +1848,7 @@ class IdentityReflectionTest(fixtures.TablesTest):
             cycle=False,
             cache=1,
         )
-        cols = insp.get_columns("t1")
+        cols = insp.get_columns(name)
         for col in cols:
             if col["name"] == "id1":
                 is_true("identity" in col)
-- 
2.47.2