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

On Sat, May 08, 2010 at 05:33:35PM -0700, Jan Urbański wrote:
> On 09/05/10 01:46, Frank Miles wrote:
> > As I have slowly been converting my system to use the adaptation
> > methods, I've been using some direct conversions to handle NULL
> > data.  It would be nice to be able to:
> > 
> >   cur.execute("SELECT {variable-list} FROM table WHERE var1 = %s AND ... ;", (variable1,...))
> > 
> > without having to pre-check each query, and alter it if one of the
> 
> How would you like it to behave in this case? You are correct, "variable
> = NULL" is definitely not the same as "variable IS NULL" and psycopg2
> cannot and will not assume what you had in mind when you wrote your
> query code. It limits itself to converting your Python objects into
> PostgreSQL literals.
> 
> > Confident that I have missed something blindingly obvious (yet haven't
> > seen it in the documentation), I ask... what's the normal way of handlng
> > this?
> 
> A wild guess: take a look at the COALESCE PostgreSQL function. But you
> haven't really explained what is it that you would like psycopg2 to do,
> so it's just a guess.
> 
> Cheers,
> Jan

Thanks for your suggestions, Jan.

What I'd like my app to be able to do is to make queries regardless of
whether the data have specific, non-NULL values {in python, the values
will be something other than None}; or be NULL {python:None}.  ISTM that
Mogrify needs to convert the "column_name = %s" into "column_name IS
NULL" ; or "column_name <> %s" into "column_name IS NOT NULL" if the
value is specified as None.  Otherwise the app - which in general must
put together a protracted series of queries in order to answer the user's
questions - must go through each of the values where the value of the
column must/not be NULL.  I don't see why psycopg shouldn't do this
(but then, see subject line).

COALESCE doesn't work.  While I suppose (not having tried it) that one
could construct a query like:
   SELECT {variable-list} FROM table WHERE COALESCE(column_name,X) = X;
this presupposes that you have a value (X) which you can guarantee will
never exist in the table.  Seems potentially dangerous and likely
less efficient.

And as perhaps wasn't completely clear in my initial question, my
concern is in structuring the WHERE clause, not altering the output of
the query to avoid Nones there.

I hope this make more sense now.

-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                -Frank
_______________________________________________
Psycopg mailing list
[email protected]
http://lists.initd.org/mailman/listinfo/psycopg