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

On Mon, May 10, 2010 at 07:48:05AM -0700, Daniele Varrazzo wrote:
> 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.

[agreed that this is flawed - snip]

> 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...

Excellent!  I had indeed overlooked this operator.  Thanks so much
for pointing this out.

> 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

Thanks, Daniele!

	-Frank