Detecting zeros in a complex type with a field of another complex type
I have a complex type with one field of another complex type:
-- Result: IS NULL = FALSE, IS NOT NULL = TRUE
-- Looks OK
CREATE TYPE bar_ok AS (id int);
CREATE TYPE foo_ok AS (val bar_ok);
CREATE OR REPLACE FUNCTION nulltest_ok()
returns foo_ok as
$$
DECLARE
_r foo_ok;
_a bool;
_b bool;
BEGIN
_a := _r IS NULL;
_b := _r IS NOT NULL;
RAISE NOTICE 'is null %', _a;
RAISE NOTICE 'is not null %', _b;
RETURN _r;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM nulltest_ok();
-- RESULT:
-- NOTICE: is null f
-- NOTICE: is not null t
Complex type with fields of complex and non-complex type:
-- Result: IS NULL = FALSE, IS NOT NULL = FALSE
-- Is that OK?
CREATE TYPE bar_bad AS (id int);
CREATE TYPE foo_bad AS (id int, val bar_bad);
CREATE OR REPLACE FUNCTION nulltest_bad()
returns foo_bad as
$$
DECLARE
_r foo_bad;
_a bool;
_b bool;
BEGIN
_a := _r IS NULL;
_b := _r IS NOT NULL;
RAISE NOTICE 'is null %', _a;
RAISE NOTICE 'is not null %', _b;
RETURN _r;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM nulltest_bad();
-- RESULT:
-- NOTICE: is null f
-- NOTICE: is not null f
-
Is it possible to check for null value of complex type with nested complex types?
-
Is it possible to get a serialized complex type without an "empty" nested complex type?
-- Type CREATE TYPE some_type AS ( id int, some_complex_type_here bar, name varchar ) -- Now - serialized complex type with nested complex type (null): (1,(),) -- Goal: (1,,)
I am using PostgreSQL 9.4.0 compiled with Visual C ++ build 1800, 64-bit (Windows 7).
source to share
Q1
- Is it possible to check for null value of complex type with nested complex types?
You are not the first to be confused by this. For simple types, IS NULL
and IS NOT NULL
are reverse operations. Either one is true or the other. But not so for inline or compound (complex) types. IS NOT NULL
returns TRUE
only if (and only if) every single column NOT NULL
. According to the documentation:
Note. If the expression has a string value, it
IS NULL
is true if the string expression itself is zero, or when all the fields in the string are null, and the valueIS NOT NULL
is true if the string expression itself is nonzero and the entire field string is not null. Because of this behavior,IS NULL
andIS NOT NULL
do not always return back results for expressions with string values, i.e. A string-valued expression containing both NULL and non-null values will return false for both tests. This definition conforms to the SQL standard [...]
The bold accent is mine.
But the complex type was initialized with NULL!
Well, not really. composite types are initialized with all nested elements to NULL, and the same is true for nested composite types recursively. And a composite type that contains a composite type that contains anything is not considered NULL in general. Rather confusing ...
There is a way around this: comparing strings is slightly different from comparing composite types . You can create a string from a decomposed type and test it with IS NULL
. This does what you are looking for. There is an example in the manual:
SELECT ROW(table.*) IS NULL FROM table; -- detect all-null rows
Or in your case:
SELECT ROW((_r).*) IS NULL;
Connected with:
- Column set NOT NULL constraint
- Why is NOT NOT NULL equal to false when checking the string type?
- Remove duplicate rows from table without unique key
Q2
- Is it possible to get a serialized complex type without an "empty" nested complex type?
Yes:
SELECT '(123,,)'::some_type;
Or:
SELECT (ROW(123, NULL, NULL))::some_type;
But as soon as you assign a plpgsql composite type variable with this, instead of setting the nested composite type to NULL altogether, each nested element is set to NULL.
The last function in my test f_noparens()
demonstrates this. It can be argued that this is a mistake and I would disagree ...
SQL Fiddle with all tests.
source to share