On Sat, Mar 14, 2015 at 7:21 PM, Denver Timothy <[email protected]> wrote: > In 9.4.1, I do this: > > CREATE TYPE my_test_type as (part1 text, part2 text); > > \pset null NULL > > WITH test_table(test_col) AS ( > VALUES (NULL::my_test_type), (ROW(NULL, NULL)::my_test_type) > ) > SELECT *, (test_col).part1, (test_col).part2, test_col IS NULL AS is_null > FROM test_table; > > And I get this result: > > ┌──────────┬───────┬───────┬─────────┐ > │ test_col │ part1 │ part2 │ is_null │ > ├──────────┼───────┼───────┼─────────┤ > │ NULL │ NULL │ NULL │ t │ > │ (,) │ NULL │ NULL │ t │ > └──────────┴───────┴───────┴─────────┘ > > But I expect this result: > > ┌──────────┬───────┬───────┬─────────┐ > │ test_col │ part1 │ part2 │ is_null │ > ├──────────┼───────┼───────┼─────────┤ > │ NULL │ NULL │ NULL │ t │ > │ NULL │ NULL │ NULL │ t │ > └──────────┴───────┴───────┴─────────┘ > > Is this expected behavior? I do find references in the docs to input/output > of NULL values as components of anonymous record types, but it's still not > clear to me if this would be expected behavior after a cast to a custom type.
kinda. The SQL standard mandates that rows containing all null values satisfy 'IS NULL = true'. However, postgres internally has nullibitily of container types that is distinct from their contents. I personally find this to be a good thing for various reasons but the facts are that postgres has some historical baggage in this area that crashed into the standard. To make things more confusing, look at: postgres=# select coalesce(row(null), row(1)); coalesce ────────── () > Is there a trick to get the result I'm expecting? So far all of the > syntactical gymnastics I can think of still produce the same result. Not really. You could make a null wrapper functions to approximate the rules you want: CREATE OR REPLACE FUNCTION NullWrap(anyelement) RETURNS anyelement AS $$ SELECT CASE WHEN $1 IS NULL THEN NULL ELSE $1 END; $$ LANGUAGE SQL IMMUTABLE; postgres=# select coalesce(nullwrap(row(null)), nullwrap(row(1))); coalesce ────────── (1) If I were to seriously consider using that often, I'd probably abbreviate it to be n() etc. merlin -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
