Re: Idiot's query: proper way to handle NULL in SELECT query?

On Mon, May 10, 2010 at 2:50 AM, Frank Miles <[email protected]> wrote:

> I was hoping that I was missing some higher-level capability within
> psycopg, but if it's not there, and there is no realistic hope that it
> will be within its capabilities, I will continue to do what I have in
> the past: essentially rewriting the query depending on whether the
> value[s] were NULL/None or not.
>
> Thanks for helping me understand the limitations of psycopg.

I will tell you how to solve the problem. Then why the solution is
deeply flawed. Then how to solve it definitely.

There is an adapter trick you can apply: wrap the object into an
object that, when adapted, adds the operator for you.

    from psycopg2.extensions import adapt, AsIs

    class Eq(object):
        def __init__(self, wrapped):
            self.wrapped = wrapped

    def adapt_eq(eq):
        if eq.wrapped is None:
            return AsIs('IS NULL')
        else:
            return AsIs("= %s" % adapt(eq.wrapped))

    psycopg2.extensions.register_adapter(Eq, adapt_eq)

This does what you need:

    >>> cur.mogrify("select foo from blah where bar %s", (Eq("O'Reilly"),))
    "select foo from blah where bar = 'O''Reilly'"

    >>> cur.mogrify("select foo from blah where bar %s", (Eq(datetime.now()),))
    "select foo from blah where bar = '2010-05-09T11:23:09.977271'"

    >>> cur.mogrify("select foo from blah where bar %s", (Eq(None),))
    'select foo from blah where bar IS NULL'

This currently works fine, but it is actually a huge hack: adaptation
should be performed on parameter values only, here it generates a
bigger chunk of expression. If Psycopg started sending queries using
different libpq functions this trick would stop working (this switch
is not currently in program anyway). This is the same reason for which
an adapter to pass table/field names to a query ("select * from
%s"...) has never been included in Psycopg.

So what? There is actually a Postgres operator that does what you
need: "IS [NOT] DISTINCT FROM": it behaves like =/<> but treats NULLs
as regular values
(http://www.postgresql.org/docs/8.4/static/functions-comparison.html):

    test=> select 10 is not distinct from 10;
    ?column? | t

    test=> select null is not distinct from 10;
    ?column? | f

    test=> select null is not distinct from null;
    ?column? | t

Well, dusty corners of the documentation...

So, Psycopg has actually some limitations: that's because it is a
driver, not a complete solution to every postgres-related need and
doesn't deal with policies, only with syntax, allowing more high level
solutions to be built upon it. It is flexible enough to allow for some
nice trick, but probably shouldn't be taken too far. In your case
probably the DISTINCT operator is the best solution.

Ah, I think POLA stands for "Principle of Least Astonishment"
(http://www.c2.com/cgi/wiki?PrincipleOfLeastAstonishment)

-- Daniele