<h3 dir="auto">Describe the bug</h3>
<p dir="auto">In order to fix <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="184305922" data-permission-text="Title is private" data-url="https://github.com/geoalchemy/geoalchemy2/issues/137" data-hovercard-type="issue" data-hovercard-url="/geoalchemy/geoalchemy2/issues/137/hovercard" href="https://github.com/geoalchemy/geoalchemy2/issues/137">geoalchemy/geoalchemy2#137</a> I would like to check that indexes are properly created in the unit tests. For this I use <code class="notranslate">from sqlalchemy import inspect</code> but it does not return the functional indexes, only the regular ones (note that the two indexes properly appear in the <code class="notranslate">table.indexes</code> entry). While when I check with <code class="notranslate">pgcli</code> the indexes are properly displayed:</p>
<div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="\d gis.test_table_with_indexes                                                                                                                                                               
+----------+-------------------+---------------------------------------------------------------------------+
| Column   | Type              | Modifiers                                                                 |
|----------+-------------------+---------------------------------------------------------------------------|
| id       | integer           |  not null default nextval('gis.test_table_with_indexes_id_seq'::regclass) |
| test     | character varying |                                                                           |
+----------+-------------------+---------------------------------------------------------------------------+
Indexes:
    &quot;test_table_with_indexes_pkey&quot; PRIMARY KEY, btree (id)
    &quot;test_functional_index&quot; btree (lower(test::text))
    &quot;test_index&quot; btree (test)
"><pre class="notranslate">\d <span class="pl-c1">gis</span>.<span class="pl-c1">test_table_with_indexes</span>                                                                                                                                                               
<span class="pl-k">+</span><span class="pl-c"><span class="pl-c">--</span>--------+-------------------+---------------------------------------------------------------------------+</span>
| Column   | Type              | Modifiers                                                                 |
|<span class="pl-c"><span class="pl-c">--</span>--------+-------------------+---------------------------------------------------------------------------|</span>
| id       | <span class="pl-k">integer</span>           |  <span class="pl-k">not null</span> default nextval(<span class="pl-s"><span class="pl-pds">'</span>gis.test_table_with_indexes_id_seq<span class="pl-pds">'</span></span>::regclass) |
| test     | character varying |                                                                           |
<span class="pl-k">+</span><span class="pl-c"><span class="pl-c">--</span>--------+-------------------+---------------------------------------------------------------------------+</span>
Indexes:
    <span class="pl-s"><span class="pl-pds">"</span>test_table_with_indexes_pkey<span class="pl-pds">"</span></span> <span class="pl-k">PRIMARY KEY</span>, btree (id)
    <span class="pl-s"><span class="pl-pds">"</span>test_functional_index<span class="pl-pds">"</span></span> btree (<span class="pl-c1">lower</span>(test::<span class="pl-k">text</span>))
    <span class="pl-s"><span class="pl-pds">"</span>test_index<span class="pl-pds">"</span></span> btree (test)
</pre></div>
<p dir="auto">I tried several combinations of regular and functional indexes and the functional ones are always missing.<br>
I also note that the primary key index is not displayed, I don't know if it is intended?</p>
<h3 dir="auto">To Reproduce</h3>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import Index
from sqlalchemy import inspect
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import func


engine = create_engine('postgresql://****************', echo=True)
metadata = MetaData(engine)
Base = declarative_base(metadata=metadata)


def test_functional_indexes():
    Base = declarative_base(metadata=metadata)

    class TestTableWithIndexes(Base):
        __tablename__ = 'test_table_with_indexes'
        __table_args__ = {'schema': 'gis'}
        id = Column(Integer, primary_key=True)
        test_col = Column(String)
        test_functional_col = Column(String)

    Index(&quot;test_functional_index&quot;, func.lower(TestTableWithIndexes.__table__.c.test_functional_col))
    Index(&quot;test_index&quot;, TestTableWithIndexes.__table__.c.test_col)

    TestTableWithIndexes.__table__.drop(engine)
    TestTableWithIndexes.__table__.create(engine)

    inspector = inspect(engine)
    indices = inspector.get_indexes(TestTableWithIndexes.__tablename__)

    print(indices)  # &lt;= ONLY &quot;test_index&quot; IN THE LIST
    assert len(indices) == 2  # FAILS"><pre class="notranslate"><span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-v">Column</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-s1">create_engine</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-v">Index</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-s1">inspect</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-v">Integer</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-v">MetaData</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-v">String</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">ext</span>.<span class="pl-s1">declarative</span> <span class="pl-k">import</span> <span class="pl-s1">declarative_base</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">sql</span> <span class="pl-k">import</span> <span class="pl-s1">func</span>


<span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-en">create_engine</span>(<span class="pl-s">'postgresql://****************'</span>, <span class="pl-s1">echo</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
<span class="pl-s1">metadata</span> <span class="pl-c1">=</span> <span class="pl-v">MetaData</span>(<span class="pl-s1">engine</span>)
<span class="pl-v">Base</span> <span class="pl-c1">=</span> <span class="pl-en">declarative_base</span>(<span class="pl-s1">metadata</span><span class="pl-c1">=</span><span class="pl-s1">metadata</span>)


<span class="pl-k">def</span> <span class="pl-en">test_functional_indexes</span>():
    <span class="pl-v">Base</span> <span class="pl-c1">=</span> <span class="pl-en">declarative_base</span>(<span class="pl-s1">metadata</span><span class="pl-c1">=</span><span class="pl-s1">metadata</span>)

    <span class="pl-k">class</span> <span class="pl-v">TestTableWithIndexes</span>(<span class="pl-v">Base</span>):
        <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">'test_table_with_indexes'</span>
        <span class="pl-s1">__table_args__</span> <span class="pl-c1">=</span> {<span class="pl-s">'schema'</span>: <span class="pl-s">'gis'</span>}
        <span class="pl-s1">id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">Integer</span>, <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
        <span class="pl-s1">test_col</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">String</span>)
        <span class="pl-s1">test_functional_col</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">String</span>)

    <span class="pl-v">Index</span>(<span class="pl-s">"test_functional_index"</span>, <span class="pl-s1">func</span>.<span class="pl-en">lower</span>(<span class="pl-v">TestTableWithIndexes</span>.<span class="pl-s1">__table__</span>.<span class="pl-s1">c</span>.<span class="pl-s1">test_functional_col</span>))
    <span class="pl-v">Index</span>(<span class="pl-s">"test_index"</span>, <span class="pl-v">TestTableWithIndexes</span>.<span class="pl-s1">__table__</span>.<span class="pl-s1">c</span>.<span class="pl-s1">test_col</span>)

    <span class="pl-v">TestTableWithIndexes</span>.<span class="pl-s1">__table__</span>.<span class="pl-en">drop</span>(<span class="pl-s1">engine</span>)
    <span class="pl-v">TestTableWithIndexes</span>.<span class="pl-s1">__table__</span>.<span class="pl-en">create</span>(<span class="pl-s1">engine</span>)

    <span class="pl-s1">inspector</span> <span class="pl-c1">=</span> <span class="pl-en">inspect</span>(<span class="pl-s1">engine</span>)
    <span class="pl-s1">indices</span> <span class="pl-c1">=</span> <span class="pl-s1">inspector</span>.<span class="pl-en">get_indexes</span>(<span class="pl-v">TestTableWithIndexes</span>.<span class="pl-s1">__tablename__</span>)

    <span class="pl-en">print</span>(<span class="pl-s1">indices</span>)  <span class="pl-c"># &lt;= ONLY "test_index" IN THE LIST</span>
    <span class="pl-k">assert</span> <span class="pl-en">len</span>(<span class="pl-s1">indices</span>) <span class="pl-c1">==</span> <span class="pl-c1">2</span>  <span class="pl-c"># FAILS</span></pre></div>
<h3 dir="auto">Error</h3>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="________________________ test_functional_indexes ________________________

    def test_functional_indexes():
        Base = declarative_base(metadata=metadata)
    
        class TestTableWithIndexes(Base):
            __tablename__ = 'test_table_with_indexes'
            __table_args__ = {'schema': 'gis'}
            id = Column(Integer, primary_key=True)
            test = Column(String)
    
        Index(&quot;test_functional_index&quot;, func.lower(TestTableWithIndexes.__table__.c.test))
        Index(&quot;test_index&quot;, TestTableWithIndexes.__table__.c.test)
    
        TestTableWithIndexes.__table__.drop(engine)
        TestTableWithIndexes.__table__.create(engine)
    
        inspector = get_inspector(engine)
        indices = inspector.get_indexes(TestTableWithIndexes.__tablename__)
    
        print(indices)
&gt;       assert len(indices) == 2
E       AssertionError: assert 1 == 2
E        +  where 1 = len([{'column_names': ['test'], 'include_columns': [], 'name': 'test_index', 'unique': False}])

tests/test_functional_index.py:36: AssertionError"><pre class="notranslate"><code class="notranslate">________________________ test_functional_indexes ________________________

    def test_functional_indexes():
        Base = declarative_base(metadata=metadata)
    
        class TestTableWithIndexes(Base):
            __tablename__ = 'test_table_with_indexes'
            __table_args__ = {'schema': 'gis'}
            id = Column(Integer, primary_key=True)
            test = Column(String)
    
        Index("test_functional_index", func.lower(TestTableWithIndexes.__table__.c.test))
        Index("test_index", TestTableWithIndexes.__table__.c.test)
    
        TestTableWithIndexes.__table__.drop(engine)
        TestTableWithIndexes.__table__.create(engine)
    
        inspector = get_inspector(engine)
        indices = inspector.get_indexes(TestTableWithIndexes.__tablename__)
    
        print(indices)
&gt;       assert len(indices) == 2
E       AssertionError: assert 1 == 2
E        +  where 1 = len([{'column_names': ['test'], 'include_columns': [], 'name': 'test_index', 'unique': False}])

tests/test_functional_index.py:36: AssertionError
</code></pre></div>
<h3 dir="auto">Versions</h3>
<ul dir="auto">
<li>OS: Linux</li>
<li>Python: 3.8.10</li>
<li>SQLAlchemy: 1.4.29</li>
<li>Database: PostgreSQL 12.9</li>
<li>DBAPI (eg: psycopg, cx_oracle, mysqlclient): psycopg2==2.9.3</li>
</ul>
<h3 dir="auto">Additional context</h3>
<p dir="auto"><em>No response</em></p>
<h3 dir="auto">Describe the use case</h3>
<p dir="auto">We are using SQLAlchemy to work with Postgres DB that has full-text indexes (<code class="notranslate">to_tsvector</code> and so on), and whenever those need a change it is unfortunate that alembic is unable to generate the automated diff. One reason for that is that <code class="notranslate">postgres</code> dialect currently does not support reflecting expression-based indexes.</p>
<p dir="auto">It is possible to live without autogenerated migrations, but we had cases when a schema change has been missed or a typo in the index expression slipped by.</p>
<h3 dir="auto">Databases / Backends / Drivers targeted</h3>
<p dir="auto">Postgres (any drivers)</p>
<h3 dir="auto">Example Use</h3>
<p dir="auto">Create table with an expression-based index</p>
<div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="create table t123123 (x varchar, y varchar);
create index t123123idx_xlowery on t123123 ((x || lower(y)));"><pre class="notranslate"><span class="pl-k">create</span> <span class="pl-k">table</span> <span class="pl-en">t123123</span> (x <span class="pl-k">varchar</span>, y <span class="pl-k">varchar</span>);
<span class="pl-k">create</span> <span class="pl-k">index</span> <span class="pl-en">t123123idx_xlowery</span> <span class="pl-k">on</span> t123123 ((x <span class="pl-k">||</span> <span class="pl-c1">lower</span>(y)));</pre></div>
<p dir="auto">Reflect the index from the live table:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="import sqlalchemy as sa
engine = sa.create_engine('postgresql://scott:tiger@172.17.0.1:5432/test')
meta = sa.MetaData()
user_table = sa.Table('t123123', meta)
insp = sa.inspect(engine)
insp.reflect_table(user_table, None)

# Current implementation will fail on the next line, because t123123idx_xlowery index is not reflected
xlowery_idx = [i for i in user_table.indexes if i.name == 't123123idx_xlowery'][0]
print(str(sa.schema.CreateIndex(xlowery_idx).compile()))
# CREATE INDEX t123123idx_xlowery ON t123123 (CAST(x AS TEXT) || lower(CAST(y AS TEXT)))"><pre class="notranslate"><span class="pl-k">import</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">as</span> <span class="pl-s1">sa</span>
<span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-en">create_engine</span>(<span class="pl-s">'postgresql://scott:tiger@172.17.0.1:5432/test'</span>)
<span class="pl-s1">meta</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-v">MetaData</span>()
<span class="pl-s1">user_table</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-v">Table</span>(<span class="pl-s">'t123123'</span>, <span class="pl-s1">meta</span>)
<span class="pl-s1">insp</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-en">inspect</span>(<span class="pl-s1">engine</span>)
<span class="pl-s1">insp</span>.<span class="pl-en">reflect_table</span>(<span class="pl-s1">user_table</span>, <span class="pl-c1">None</span>)

<span class="pl-c"># Current implementation will fail on the next line, because t123123idx_xlowery index is not reflected</span>
<span class="pl-s1">xlowery_idx</span> <span class="pl-c1">=</span> [<span class="pl-s1">i</span> <span class="pl-k">for</span> <span class="pl-s1">i</span> <span class="pl-c1">in</span> <span class="pl-s1">user_table</span>.<span class="pl-s1">indexes</span> <span class="pl-k">if</span> <span class="pl-s1">i</span>.<span class="pl-s1">name</span> <span class="pl-c1">==</span> <span class="pl-s">'t123123idx_xlowery'</span>][<span class="pl-c1">0</span>]
<span class="pl-en">print</span>(<span class="pl-en">str</span>(<span class="pl-s1">sa</span>.<span class="pl-s1">schema</span>.<span class="pl-v">CreateIndex</span>(<span class="pl-s1">xlowery_idx</span>).<span class="pl-en">compile</span>()))
<span class="pl-c"># CREATE INDEX t123123idx_xlowery ON t123123 (CAST(x AS TEXT) || lower(CAST(y AS TEXT)))</span></pre></div>
<h3 dir="auto">Additional context</h3>
<p dir="auto">Adding <code class="notranslate">pg_get_expr(ix.indexprs, ix.indrelid)</code> to the list of index metadata requested in  <code class="notranslate">sa.dialects.postgres.base.PGDialect.get_indexes</code> should be straightforward. Also, there are parsers for Postgres code (<a href="https://github.com/pganalyze/libpg_query">https://github.com/pganalyze/libpg_query</a> and derivatives) that should make the process of converting Postgres code into SA expression objects easier.</p>