Postgres to-json converts a composite type into empty structure

Question

in the following code example postgresql (9.4) is supposed to transform a local variable of a composite type into a json string. apparently postgresql handle a variable with null value different than a null-value cast to the same type.

in my application i have nested composite types and would like to retrieve null when a variable of a composite type has null as a value.

i hope on of you guys can help me out here.

CREATE TYPE mytype AS (
    id SMALLINT,
    name TEXT
);

DO $$
DECLARE
    l_var mytype;
BEGIN
    l_var := NULL;
    RAISE INFO '%',to_json(NULL::mytype);
    RAISE INFO '%',to_json(l_var) ;
END $$; 

--Output
INFO:  <NULL>
INFO:  {"id":null,"name":null}

Huh, so in PL/PgSQL assigning `NULL` to a record var is different to passing a `NULL` record var to a function. I would not have guessed that. What an odd wart. — Craig Ringer, May 06 '15 at 08:26

score 3 · Accepted Answer · answered May 06 '15 at 07:03

3

yes, PostgreSQL distinguish between ROW with empty fields and NULL. A fix should not be hard - you should to use CASE expression:

postgres=# DO $$
DECLARE
    l_var mytype;
BEGIN
    l_var := NULL;
    RAISE INFO '%',to_json(NULL::mytype);
    RAISE INFO '%',to_json(CASE WHEN l_var IS NULL THEN NULL ELSE l_var END) ;
END $$; 
INFO:  <NULL>
INFO:  <NULL>
DO

answered May 06 '15 at 07:03

Pavel Stehule

42,331
5
91
94

Well, more workaround than fix. If I saw that in someone's code without an explanatory comment I'd be doing some serious "WTF?". – Craig Ringer May 06 '15 at 08:27
1

@CraigRinger - it is result of difference between RECORD and ROW types in Postgres. ROW types has structure every time, so it cannot be scalar NULL. Some years ago this question was opened, and the result was - The ANSI SQL requires this behave, if I remember wall. – Pavel Stehule May 06 '15 at 09:23

score 0 · Answer 2 · answered May 07 '15 at 07:56

0

Unfortunatly pavels solution doesn't work for me, because it's used in another type and i cant apply the proposed case-structure then.

But after talking to the backend devlopers, ist better for them to always receive a full json structure even tho the fields have no value. So from the symantic Point of view, postgres works fine.

answered May 07 '15 at 07:56

RootOfProblem

357
1
3
11

1

There is another possibility in prepared PostgreSQL 9.5. You can use a json_strip_nulls function, that reduce json `{"id":null,"name":null}` to `{}` – Pavel Stehule May 07 '15 at 09:04

Postgres to-json converts a composite type into empty structure

2 Answers2