Re: NULLs and composite types

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.