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 > > 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. > """" > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
