CODEWITHSUNDEEP
postgresqlsqlalchemy
Adam Matan
Adam Matan

Reputation: 136421

SQLAlchemy operator for PostgreSQL's aggregate (every)

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()?

Upvotes: 2

Views: 229

Answers (1)

Marth
Marth

Reputation: 24822

You can use the func object to generate SQL functions.

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

will generate

HAVING EVERY(stars.landed_upon)

Upvotes: 1

Related Questions