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

I can't test it at the moment but .having(func.every(Star.landed_upon)) should work. — Marth
– Marth, Commented Jun 10, 2015 at 13:03
@Marth Excellent, it works. Care to make it an answer so I can accept it? — Adam Matan
– Adam Matan, Commented Jun 10, 2015 at 16:00

Marth · Accepted Answer · 2015-06-12 13:06:10Z

1

You can use the func object to generate SQL functions.

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

will generate

HAVING EVERY(stars.landed_upon)

answered Jun 12, 2015 at 13:06

Marth

24.9k3 gold badges68 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLAlchemy operator for PostgreSQL's aggregate (every)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related