Postgres: Reflection of indexes with operators

[garyvdm]

Describe the use case

Postgress allows for creating GIST indexes with opperators, such as inet_ops:

CREATE INDEX ON my_table USING GIST (my_inet_column inet_ops);

I would like to make sqlalchemy correctly reflect these indexes.

Databases / Backends / Drivers targeted

Postgress database, all postgres drivers

Example Use

import testing.postgresql

from sqlalchemy import Column, Index, MetaData, Table, create_engine
from sqlalchemy.dialects.postgresql import INET
from sqlalchemy.sql import literal_column

source_metadata = MetaData()

Table(
    "t",
    source_metadata,
    Column("addr", INET),
    source_index := Index(
        "ix_1",
        literal_column("addr inet_ops"),
        postgresql_using="GiST",
    ),
)

with testing.postgresql.Postgresql() as postgresql:
    engine = create_engine(postgresql.url())
    source_metadata.create_all(engine)

    reflected_metadata = MetaData()
    reflected_metadata.reflect(bind=engine)
    reflected_index = list(reflected_metadata.tables["t"].indexes)[0]

print("source:    ", source_index)
print("reflected: ", reflected_index)
assert source_index == reflected_index

source:     Index('ix_1', <sqlalchemy.sql.elements.ColumnClause at 0x7f11f95a2da0; addr inet_ops>)
reflected:  Index('ix_1', Column('addr', INET(), table=<t>))
Traceback (most recent call last):
  File "/home/gary/dev/sqlalchemy/test_inet_ops_reflection.py", line 33, in <module>
    assert source_index == reflected_index
AssertionError

Additional context

If I can get this fixed, I'm hoping I can use it to fix https://github.com/sqlalchemy/alembic/issues/1098

[CaselIT]

Hi,

Hopefully it's not hard to get this information. Can we just treat these as expressions and place them in the expressions list?

[CaselIT]

Forgot to ping @zzzeek in the above question

[zzzeek]

these would be expressions, sure.

[CaselIT]

ok, so the only thing to add would be the actual reflection of these