3

In PostgreSQL 9.4.1 I have created a custom type my_test_type and have found that doing a basic INSERT with NULL::my_test_type gives an expected result, but with NULL::my_test_type as a function argument and using the exact same query, I get an unexpected result:

CREATE TYPE my_test_type as (part1 text, part2 text);
CREATE TABLE my_test_table (id serial, test_col my_test_type);
CREATE FUNCTION my_test_table_insert (test_arg my_test_type DEFAULT NULL)
  RETURNS VOID LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO my_test_table (test_col) VALUES (test_arg);
END; $$;

-- gives null as expected
SELECT NULL::my_test_type;

-- test_col value will be null as expected
INSERT INTO my_test_table (test_col) VALUES (NULL);

-- this performs the exact same query above but in a function,
-- but test_col value shows '(,)', which is NOT expected
SELECT my_test_table_insert(NULL);

-- this is what seems to happening in the function, but I don't understand why
INSERT INTO my_test_table(test_col) VALUES (ROW(NULL, NULL)::my_test_type);

\pset null 'NULL'
SELECT *, (test_col).part1, (test_col).part2, test_col IS NULL AS is_null FROM my_test_table;
┌────┬──────────┬───────┬───────┬─────────┐
│ id │ test_col │ part1 │ part2 │ is_null │
├────┼──────────┼───────┼───────┼─────────┤
│  1 │ NULL     │ NULL  │ NULL  │ t       │
│  2 │ (,)      │ NULL  │ NULL  │ t       │
│  3 │ (,)      │ NULL  │ NULL  │ t       │
└────┴──────────┴───────┴───────┴─────────┘
(3 rows)

Even though everything tests as null, why does the result from the INSERT inside the function show differently in the table?

Edit: The problem can be simplified:

VALUES (NULL::my_test_type), (ROW(NULL, NULL)::my_test_type);
┌─────────┐
│ column1 │
├─────────┤
│ NULL    │
│ (,)     │
└─────────┘
(2 rows)
1

Merlin Moncure from the PostgreSQL General mailing list has an answer:

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.