1

When I construct an hstore

CREATE EXTENSION hstore;
SELECT hstore(null::my_type);
                     hstore                     
------------------------------------------------
 "name"=>NULL, "street"=>NULL, "location"=>NULL
(1 row)

While the jsonb method,

SELECT to_jsonb(null::my_type);
 to_jsonb 
----------

(1 row)

This creates a surprising effect too when you try to merge (||) another like type,

SELECT hstore(null::my_type) || hstore('name', 'Evan');
                     ?column?                     
--------------------------------------------------
 "name"=>"Evan", "street"=>NULL, "location"=>NULL
(1 row)

SELECT to_jsonb(null::my_type) || jsonb_build_object('name', 'Evan');
 ?column? 
----------

(1 row)
0

The workaround here is to construct the jsonb by proxy of jsonb_populate_record

SELECT jsonb_populate_record(
  null::my_type,
  '{}'
);

Or, in the event you're just trying to update the record type, you can do it directly with the one call.

SELECT jsonb_populate_record(
  null::my_type,
  jsonb_build_object('name', 'Evan')
);
  • 1
    to_jsonb() function declared as STRICT so it always returns NULL for NULL argument. to_jsonb('(,,)'::my_type) should to work as you expecting. – Abelisto Sep 16 '18 at 19:10

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.