Null detection in a complex type with field of another complex type

Question

I have a complex type with a single 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 test null value of complex type with nested complex types?

Is it possible to get serialized complex type without "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 running PostgreSQL 9.4.0, compiled by Visual C++ build 1800, 64-bit (Windows 7).

Erwin Brandstetter · Accepted Answer · 2019-01-24T01:28:48.280

Q1

Is it possible to test 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 inverse operations. Either one is true, or the other. But not so for row-valued or composite (complex) types. IS NOT NULL only returns TRUE if (and only if) every single column is NOT NULL. Per documentation:

Note: If the expression is row-valued, then IS NULL is true when the row expression itself is null or when all the row's fields are null, while IS NOT NULL is true when the row expression itself is non-null and all the row's fields are non-null. Because of this behavior, IS NULL and IS NOT NULL do not always return inverse results for row-valued expressions, i.e., a row-valued expression that contains both NULL and non-null values will return false for both tests. This definition conforms to the SQL standard [...]

Bold emphasis mine.

But the complex type was initialized with NULL!

Well, not exactly. composite types are initialized with all nested elements NULL, the same is true for nested composite types recursively. And a composite type containing a composite type containing anything is not considered NULL as a whole. Rather confusing ...

There is a way to get around this: row-wise comparison is slightly different from composite type comparison. You can construct a row from the decomposed type and test 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;

Is it possible to get serialized complex type without "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 variable of composite type with this, instead of setting the nested composite type NULL as a whole, each nested element is set to NULL.

The last function in my test f_noparens() demonstrates this. One might argue this is a bug and I wouldn't disagree ...

SQL Fiddle with all tests.

Null detection in a complex type with field of another complex type

1 Answers1

Q1

Q2