SOME/ANY/ALL in postgres?

[Ryan Kelly]

I'm trying to figure out the correct way to use these array comparisons
features specific to postgres, e.g.:

select * from foo where 1 = any(bar);

So I tried this:

from sqlalchemy.sql.expression import func
session.query(foo).filter(1 == func.any(foo.c.bar))

But that didn't work, as I got this (essentially):

select * from foo where any(bar) = 1;

Well, then I tried this:

from sqlalchemy.sql.expression import func, literal
session.query(foo).filter(literal(1) == func.any(foo.c.bar))

And that was better:

select * from foo where 1 = any(bar);

Unfortunately I really wanted something like this:

select * from foo where not 1 = any(bar);

So I tried this:

from sqlalchemy.sql.expression import func, literal, not_
session.query(foo).filter(not_(literal(1) == func.any(foo.c.bar)))

Which gave me this:

select * from foo where 1 != any(bar);

Which is not correct. Of course I could do this:

from sqlalchemy.sql.expression import func, literal, not_
session.query(foo).filter(not_(literal(1) == func.all(foo.c.bar)))

But I should really ask here for help because I've loaded the foot-gun
completely full.

So, what's the "correct" way to do this?

-Ryan Kelly

[Michael Bayer]

On Jan 11, 2013, at 4:37 PM, Ryan Kelly wrote:

> I'm trying to figure out the correct way to use these array comparisons
> features specific to postgres, e.g.:
>
> select * from foo where 1 = any(bar);
>
> So I tried this:
>
> from sqlalchemy.sql.expression import func
> session.query(foo).filter(1 == func.any(foo.c.bar))
>
> But that didn't work, as I got this (essentially):
>
> select * from foo where any(bar) = 1;

reading this, it seemed extremely surprising that the = operator would suddenly not be commutative. So I took a look at "ANY" in the PG docs, and I don't really see this as the "=" operator anymore, it's a special PG operator that is essentially "= ANY". So we can use op() for that. Then, to build this with off-the-shelf pieces, we have to jump through the many hoops of this very non-standard syntax:

from sqlalchemy.dialects import postgresql
from sqlalchemy import literal, tuple
from sqlalchemy.dialects.postgresql import array

literal(1).op("= ANY")(tuple_(array([foo.c.bar])))

then the NOT case (~ is equivalent to not_()):

expr = (~literal(1)).op("= ANY")(tuple_(array([foo.c.bar])))

seems to work:

print expr.compile(dialect=postgresql.dialect())

NOT %(param_1)s = ANY (ARRAY[foo.bar])

now that's cobbling it together with off the shelf pieces, which you could of course throw into a function called any() to hide that boilerplate. But also, we can make this into a construct where we can build up the string directly (and this you can use to build anything without too much guessing):

from sqlalchemy.types import Boolean
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.compiler import OPERATORS
from sqlalchemy.sql.expression import ColumnElement, literal

class Any(ColumnElement):
type = Boolean()

def __init__(self, left, right, operator=operator.eq):
self.left = literal(left)
assert isinstance(right, array)
self.right = right
self.operator = operator

@compiles(Any, "postgresql")
def compile_any(element, compiler, **kw):
return "%s%sANY(%s)" % (
compiler.process(element.left),
OPERATORS[element.operator],
compiler.process(element.right)
)

print Any(1, array([foo.c.bar])).compile(dialect=postgresql.dialect())
# %(param_1)s = ANY(ARRAY[foo.bar])

print (~Any(1, array([foo.c.bar]))).compile(dialect=postgresql.dialect())
# NOT %(param_1)s = ANY(ARRAY[foo.bar])

These functions (ANY, ALL, etc.) should probably be provided as part of sqlalchemy.dialects.postgresql to start with as well as be available as methods from the ARRAY comparator.