how can i generate " IS NOT True " instead of " != True " ( orm ) ?

[Jonathan Vanasco]

in postgresql i have a boolean field that allows Null values.

i'd like to query for the items that are not 'true'

filter( tablename.is_deleted != True )

creates this sql:
is_deleted != True

however this is incorrect and doesn't match the resultset i want. it
needs to read :
is_deleted IS NOT True

I was hoping that i could do TableName.column_name.not_( True ) , but
that didn't work.

in postgresql ( and they claim the sql standard), !=/== and "IS
NOT"/"IS" are different types of comparisons.

http://www.postgresql.org/docs/8.2/static/functions-comparison.html

"""
Do not write expression = NULL because NULL is not "equal to" NULL.
(The null value represents an unknown value, and it is not known
whether two unknown values are equal.) This behavior conforms to the
SQL standard.


The ordinary comparison operators yield null (signifying "unknown")
when either input is null. Another way to do comparisons is with the
IS [ NOT ] DISTINCT FROM construct:

expression IS DISTINCT FROM expression
expression IS NOT DISTINCT FROM expression
For non-null inputs, IS DISTINCT FROM is the same as the <> operator.
However, when both inputs are null it will return false, and when just
one input is null it will return true. Similarly, IS NOT DISTINCT FROM
is identical to = for non-null inputs, but it returns true when both
inputs are null, and false when only one input is null. Thus, these
constructs effectively act as though null were a normal data value,
rather than "unknown".

Boolean values can also be tested using the constructs

expression IS TRUE
expression IS NOT TRUE
expression IS FALSE
expression IS NOT FALSE
expression IS UNKNOWN
expression IS NOT UNKNOWN
These will always return true or false, never a null value, even when
the operand is null. A null input is treated as the logical value
"unknown". Notice that IS UNKNOWN and IS NOT UNKNOWN are effectively
the same as IS NULL and IS NOT NULL, respectively, except that the
input expression must be of Boolean type.
""""

[David Bolen]

Jonathan Vanasco <jona...@findmeon.com> writes:

> in postgresql i have a boolean field that allows Null values.
>
> i'd like to query for the items that are not 'true'
>
> filter( tablename.is_deleted != True )
>
> creates this sql:
> is_deleted != True
>
> however this is incorrect and doesn't match the resultset i want. it

To be fair, it is correct in terms of doing what you asked, though if
you want it to include NULLs I agree it doesn't do what you want...

> needs to read :
> is_deleted IS NOT True

There are "is" and "isnot" operators in sqlalchemy.sql.operators, but I'm
not entirely sure how to trigger from within an expression (and they don't
seem to be column operators, but I'm probably missing something simple).

However, you should be able to use the generic "op" method, as in:

filter(tablename.is_deleted.op("IS NOT")(True))

Alternatively, you could explicitly manage the handling of NULL
entries:

from sqlalchemy.sql.functions import coalesce

filter(coalesce(tablename.is_deleted, False) != True)

This way you avoid the treatment of NULLs in the comparison entirely. This
method also extrapolates to other cases more readily since it works with
non-boolean fields as well.

-- David

[Jonathan Vanasco]

> To be fair, it is correct in terms of doing what you asked, though if
> you want it to include NULLs I agree it doesn't do what you want...

You're absolutely correct. Poorly worded on my part. I meant to
convey that it's not the correct statement for me to call ; it is
indeed the correct sql for that statement. "!=" and "is not" are
completely different comparisons.

I just spent 4 hours digging through sqlalchemy docs and code to try
and figure out how to get this to work on columns. is & isnot should
really be column operators. there's also no "UNKNOWN" value/keyword
in sqlalchemy -- which is a concept that is in postgresql and might
have helped me in my wild chase.

Anyways, Thanks a ton!

filter(tablename.is_deleted.op("IS NOT")(True)) works perfectly ,
crisis averted!

Your coalesce idea is a good one. I feel a little bit better using
the .op() method though - this query is already kind of complex and
slow. adding a handful of coalesce statements instead of using a
native comparison is not a road i want to explore right now.

[Michael Bayer]

On Aug 10, 2012, at 3:24 PM, Jonathan Vanasco wrote:

> in postgresql i have a boolean field that allows Null values.
>
> i'd like to query for the items that are not 'true'
>
> filter( tablename.is_deleted != True )
>
> creates this sql:
> is_deleted != True
>
> however this is incorrect and doesn't match the resultset i want. it
> needs to read :
> is_deleted IS NOT True
>
> I was hoping that i could do TableName.column_name.not_( True ) , but
> that didn't work.

this is an API omission and http://www.sqlalchemy.org/trac/ticket/2544 is added.

There is direct support for "IS" "IS NOT", and to workaround the lack of the method looks like this:

from sqlalchemy.sql import column
from sqlalchemy.sql.expression import _BinaryExpression
from sqlalchemy.sql import operators

def is_(a, b):
return _BinaryExpression(a, b, operators.is_, negate=operators.isnot)

print is_(column("x"), True)
print ~is_(column("x"), True)

output:

x IS true
x IS NOT true

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>