SQLAlchemy operator for PostgreSQL's aggregate (every)

Question

PostreSQL features the every() aggregate functions, which filters groups by running a predicate on all group rows.

For example, for the following table:

 star_name | star_type | landed_upon
-----------+-----------+-------------
 Mars      | Planet    | t
 Venus     | Planet    | t
 Rhea      | Moons     | f
 Titan     | Moons     | t

This every() query:

SELECT star_type, max(star_name) example, COUNT(*)
    FROM stars
    GROUP BY star_type
    HAVING every (landed_upon = true);

Returns the Planet group but not the Moons group, because Rhea does not satisfy the landed_upon predicate within the Moons group:

 star_type | example | count
-----------+---------+-------
 Planet    | Venus   |     2
(1 row)

Is there an equivalent SQLAlchemy operator for PostgreSQL every()?

Answer 1

You can use the func object to generate SQL functions.

.having(func.every(Star.landed_upon))

will generate

HAVING EVERY(stars.landed_upon)