Postgresql COLLATE requires quotes

import sqlalchemy as sa
e = sa.create_engine(
    'postgresql://scott:tiger@localhost/test', echo='debug')


m = sa.MetaData()
t = sa.Table('table', m, sa.Column('column', sa.TEXT()))
with e.connect() as conn:
    trans = conn.begin()
    t.create(bind=conn)

    # fails
    s = sa.select([t]).order_by(t.c.column.collate('C'))

    # works
    # s = sa.select([t]).order_by(t.c.column.collate('"C"'))

    trans.rollback()

the implementation here would need to build out a new Collate() construct that's intercepted by each dialect. Right now BinaryExpression(expr, _literal_as_text(collation)) is hardwired into elements.py.

PG dialect would also need to anticipate existing quotes and warn if they're present.

Comments (2)