Re: When is a record NULL? - Mailing list pgsql-hackers

From Jeff Davis
Subject Re: When is a record NULL?
Date
Msg-id 1248453622.5201.88.camel@jdavis
Whole thread Raw
In response to Re: When is a record NULL?  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: When is a record NULL?  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: When is a record NULL?  ("David E. Wheeler" <david@kineticode.com>)
List pgsql-hackers
On Fri, 2009-07-24 at 09:31 -0400, Tom Lane wrote:
> Brendan Jurd <direvus@gmail.com> writes:
> It's worse than that, because there actually is also such a thing as
> the row value being NULL --- ie, there's no row structure at all.
> At least internally, that's a completely different thing from having
> a row all of whose fields are null.

Here is an example: select * from (values (row(NULL)), (null)) v;

But it's hard to do much useful with row values once you have them. You
can't even count them: select count(column1) from (values (row(NULL)), (null)) v; ERROR:  record type has not been
registered

> SQL doesn't provide a test for this case that's separate from the test
> involving null-ness of individual fields.  Not much we can do about
> it though.  I'm not entirely sure that exposing the distinction would
> be helpful anyway ...

There's no single test, but you can see if it's an empty row with: x IS NULL AND x IS DISTINCT FROM NULL
And you can see if it's a "real" NULL by: x IS NULL AND x IS NOT DISTINCT FROM NULL

It's funny until you try it -- then it's just scary.

Regards,Jeff Davis 



pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: bytea vs. pg_dump
Next
From: Emanuel Calvo Franco
Date:
Subject: uuid contrib don't compile in OpenSolaris