agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
To: pgsql-sql@postgresql.org
Subject: Re: NULLs and composite types
Date: Thu, 12 Dec 2013 13:57:42 -0800
Message-ID: <52AA3156.20902@ultimeth.com> (raw)
In-Reply-To: <1386876331115-5783187.post@n5.nabble.com>
References: <52A9010D.3070202@ultimeth.com>
<1386876331115-5783187.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
On 2013-12-12 11:25, David Johnston wrote:
> Dean Gibson (DB Administrator)-2 wrote
>> What's going on? I can provide more detail if requested. Of course, an
>> obvious workaround is to use in a VIEW:
>>
>> ... NULLIF( location, ROW( NULL, NULL )::"GeoPosition" ) ...
>>
>> but I'd like to know the cause.
> Cannot test right now but the core issue is that IS NULL on a record type
> evaluates both the scalar whole and the sub-components. Try using IS [NOT]
> DISTINCT FROM with various target expressions and see if you can get
> something more sane.
>
> David J.
Yes, "SELECT ROW( NULL, NULL ) IS NULL;" produces TRUE, and "SELECT ROW(
NULL, NULL ) IS NOT DISTINCT FROM NULL;" produces FALSE.
However, my problem is not that the comparison tests produce different
results; that's just a symptom. My problem is that PostgreSQL is
*changing* a NULL record value, to a record with NULLs for the component
values, when I attempt to INSERT or UPDATE it into a different field.
That means in php (for example), that retrieving what started out as a
NULL record (and in php retrieves an empty string), becomes a record
with NULL values (and in php retrieves a "(,)" string). Yes, I can test
for that in php, but problems/work-arounds need to be solved in the
component that causes them.
However, I have found a satisfactory work-around in the TRIGGER function
to the problem: In my INSERT and UPDATE statements, I use:
... NULLIF( record_row.location, ROW( NULL, NULL )::"GeoPosition" ) ...
when adding or changing a value.
Note that setting "record_row.location" to NULL in PL/pgSQL just before
the INSERT or UPDATE *does not solve the problem*, and tests of the
value before and after setting the value in a record field (retrieved
via a CURSOR FOR SELECT ...) shows that the value does not change to
fully NULL.
--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.
view thread (5+ messages) latest in thread
Message-ID: <52AA3156.20902@ultimeth.com>
Permalink: ../52AA3156.20902@ultimeth.com/
Also on: postgresql.org/message-id/52AA3156.20902@ultimeth.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql@ultimeth.com
Subject: Re: NULLs and composite types
In-Reply-To: <52AA3156.20902@ultimeth.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox