PostgreSQL now reflects per-column sort order on indexes.

Added support for column sorting flags when reflecting indexes for
PostgreSQL, including ASC, DESC, NULLSFIRST, NULLSLAST.  Also adds this
facility to the reflection system in general which can be applied to other
dialects in future releases.  Pull request courtesy Eli Collins.

Fixes: #4717
Closes: #4725
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/4725
Pull-request-sha: 3cbb067bd46776fdb125553ba0ac192cb45d060c

Change-Id: I8b0617d68580cfe4ff79d758a077263f33e852c2

1 files changed, 77 insertions, 0 deletions

diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py
index f2e491167..9580018be 100644
--- a/test/dialect/postgresql/test_reflection.py
+++ b/test/dialect/postgresql/test_reflection.py
@@ -907,6 +907,83 @@ class ReflectionTest(fixtures.TestBase):
             ],
         )
 
+    @testing.fails_if("postgresql < 8.3", "index ordering not supported")
+    @testing.provide_metadata
+    def test_index_reflection_with_sorting(self):
+        """reflect indexes with sorting options set"""
+
+        t1 = Table(
+            "party",
+            self.metadata,
+            Column("id", String(10), nullable=False),
+            Column("name", String(20)),
+            Column("aname", String(20)),
+        )
+
+        with testing.db.connect() as conn:
+
+            t1.create(conn)
+
+            # check ASC, DESC options alone
+            conn.execute(
+                """
+                create index idx1 on party
+                    (id, name ASC, aname DESC)
+            """
+            )
+
+            # check DESC w/ NULLS options
+            conn.execute(
+                """
+              create index idx2 on party
+                    (name DESC NULLS FIRST, aname DESC NULLS LAST)
+            """
+            )
+
+            # check ASC w/ NULLS options
+            conn.execute(
+                """
+              create index idx3 on party
+                    (name ASC NULLS FIRST, aname ASC NULLS LAST)
+            """
+            )
+
+        # reflect data
+        with testing.db.connect() as conn:
+            m2 = MetaData(conn)
+            t2 = Table("party", m2, autoload=True)
+
+        eq_(len(t2.indexes), 3)
+
+        # Make sure indexes are in the order we expect them in
+        r1, r2, r3 = sorted(t2.indexes, key=lambda idx: idx.name)
+
+        eq_(r1.name, "idx1")
+        eq_(r2.name, "idx2")
+        eq_(r3.name, "idx3")
+
+        # "ASC NULLS LAST" is implicit default for indexes,
+        # and "NULLS FIRST" is implicit default for "DESC".
+        # (https://www.postgresql.org/docs/11/indexes-ordering.html)
+
+        def compile_exprs(exprs):
+            return list(map(str, exprs))
+
+        eq_(
+            compile_exprs([t2.c.id, t2.c.name, t2.c.aname.desc()]),
+            compile_exprs(r1.expressions),
+        )
+
+        eq_(
+            compile_exprs([t2.c.name.desc(), t2.c.aname.desc().nullslast()]),
+            compile_exprs(r2.expressions),
+        )
+
+        eq_(
+            compile_exprs([t2.c.name.nullsfirst(), t2.c.aname]),
+            compile_exprs(r3.expressions),
+        )
+
     @testing.provide_metadata
     def test_index_reflection_modified(self):
         """reflect indexes when a column name has changed - PG 9