Can't use a CAST in a PostgreSQL ExcludeConstraint

[v]

In PostgreSQL I can create a table with an exclusion constraint involving a CAST (the CAST is necessary because the UUID type doesn't have a default operator class for gist):

CREATE EXTENSION btree_gist;
CREATE TABLE example (
  id         UUID,
  some_range INT4RANGE,
  EXCLUDE USING gist (CAST("id" AS TEXT) WITH =, some_range WITH &&)
);

When I try to do the same in SQLAlchemy:

from sqlalchemy import *
from sqlalchemy.dialects.postgresql import (
    UUID, INT4RANGE, ExcludeConstraint, TEXT
)

class Example(Base):
    __tablename__ = 'example'
    id = Column(UUID)
    some_range = Column(INT4RANGE)
    __table_args__ = (
        ExcludeConstraint(
            (cast('id', TEXT), '='), ('some_range', '&&')
        ),
    )

I get the error sqlalchemy.exc.ArgumentError: Can't add unnamed column to column collection.

('id::TEXT', '=') doesn't work because SQLAlchemy doesn't recognize 'id::TEXT' as a column.

[Mike Bayer]

this is a workaround that makes it through to the DB, though I still dont know the syntax PG wants:

from sqlalchemy.sql.elements import quoted_name

        ExcludeConstraint(
            (column(quoted_name('id::text', quote=False)), '='), ('some_range', '&&')
        ),

[Mike Bayer]

• Repaired the :class:.ExcludeConstraint construct to support common features that other objects like :class:.Index now do, that the column expression may be specified as an arbitrary SQL expression such as :obj:.cast or :obj:.text. fixes #3454

→ <<cset 4a25c10e2714>>

[Mike Bayer]

thanks for reporting!

[v]

Thanks for providing a workaround and for the very quick turnaround!