SQLAlchemy not equals on postgres

Question

I was going to log an issue on the SQLAlchemy site, but before I did I just wanted to check I hadn't missed anything. I have asked on IRC and I think this could possibly be a legit issue.

What it boils down to is the != filters are not taking into account empty/NULL cells.

So lets say for instance

Session.query(Table).\
filter(Table.column != 1).all()

Now I would expect this to return all records where the value was not equal to 1. However, what it actually returns is a list of records that have data in, that isn't equal to 1.

So if for example column was a NULL/empty value, it would not be returned by this query, to which I (and others) would expect it to be.

Now obviously it's best practice to use NOT NULL on creating columns, but I just wanted to see if there was a fix for this, or whether its worth filing a bug

sayap · Accepted Answer · 2013-03-04 11:58:30Z

1

Well, this has nothing to do with SQLAlchemy, it is just how NULL rolls. NULL != 1 is neither true or false, it evaluates to NULL. Thus, the WHERE clause won't return any row with NULL value for the column.

You can go with either:

Session.query(Table).filter(or_(
    Table.column == None,
    Table.column != 1,
))

or something like:

Session.query(Table).filter(
    func.coalesce(Table.column, -1) != 1)

to transform NULL values into something usable for comparison, as -1 != 1 evaluates to true.

answered Mar 4, 2013 at 11:58

sayap

6,2852 gold badges38 silver badges40 bronze badges

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

1 Comment

Cro0ksey Over a year ago

Thanks, my production database uses not null on the columns, I was just testing this locally and found it. Thanks for clearing it up :)

Collectives™ on Stack Overflow

SQLAlchemy not equals on postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related