Sqlalchemy: DISTINCT ON (Only disctinct)

[rudmac]

with SQLAlchemy 1.3.3

after executing this code:

result = session.query(LS_Cointegration.rank).distinct(LS_Cointegration.rank).order_by(LS_Cointegration.rank)

why ON (distinct on) is not compiled?

print(result.statement.compile(compile_kwargs={"literal_binds": True}))

Only disctinct

SELECT DISTINCT ls_cointegration.rank
FROM ls_cointegration ORDER BY ls_cointegration.rank

Is this a issue or I misunderstood the use of distinct?

Thanks

[zzzeek]

DISTINCT ON is Postgresql specfic so if you are running print you need to use the postgresql dialect, see https://docs.sqlalchemy.org/en/13/faq/sqlexpressions.html#stringifying-for-specific-databases

[rudmac]

Of course, thank you so much

[zzzeek]

sorry I've tried to improve this "Stringiy the SQL" thing as much as I can but I haven't found a good way to make these situations obvious...I guess long term maybe print() str() should just not compile into SQL unless you give it a dialect (and then maybe you can pass dialect as a string name also).

[zzzeek]

or maybe it should compile as "... DISTINCT ..."