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.

With the SQLAlchemy ORM (using the declarative extension,) here is one way to write a query to retrieve all unique names from a name table:

>>> 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.