Hi there. Long time no post.
I just spent a couple hours debugging a query with a DISTINCT ON clause. The DISTINCT ON clause is a PostgreSQL-specific extension to the standard DISTINCT clause, which lets you specify the columns on which you want PostgreSQL to operate for filtering out duplicate rows.
>>> q = DBSession.query(model.Name).distinct(model.Name.value) \ .order_by(model.Name.value)
Now, if you’d want to see what kind of SQL query the ORM will generate you’d print it like so:
>>> print str(q) SELECT DISTINCT name.id AS name_id, name.value AS name_value FROM name ORDER BY name.value
But as you can see, there’s no DISTINCT ON here. Dang.
However, and that’s the real catch, the database will actually receive a correct query, with a complete DISTINCT ON clause. What happens is that when printing the query, SQLAlchemy will not use the PostgreSQL dialect for compiling it, as the output (a plain string) is not a PG database.
If you want to take a peek at what is going to be sent to the DB, you need to tell the SQLAlchemy compiler what dialect it should be using. Here’s a way to print exactly how it’s going to be compiled to:
>>> from sqlalchemy.dialects import postgresql >>> print str(q.statement.compile(dialect=postgresql.dialect())) SELECT DISTINCT ON (name.value) name.id, name.value FROM name ORDER BY name.value
This is not an obvious one, and I’ve been mystified for a while with things that shouldn’t be working but did work anyway. Anyway, mystery solved, and I hope this post will help someone else.