Postgres Pro
Downloads
Home   >   mailing lists

Re: [GENERAL] Concatenating NULL with JSONB value return NULL - Mailing list pgsql-general

From Melvin Davidson
Subject Re: [GENERAL] Concatenating NULL with JSONB value return NULL
Date
Msg-id CANu8FiwcGP9NMtGqoTszT+r6hgXS0J1C7zLmvREO_ie7ExEGWw@mail.gmail.com
Whole thread Raw
In response to Re: [GENERAL] Concatenating NULL with JSONB value return NULL  (John R Pierce <pierce@hogranch.com>)
Responses Re: [GENERAL] Concatenating NULL with JSONB value return NULL
List pgsql-general
Tree view


On Sun, Dec 18, 2016 at 6:08 PM, John R Pierce <pierce@hogranch.com> wrote:
On 12/18/2016 2:52 PM, Jong-won Choi wrote:

I have a NULL-able JSONB type column and want to perform upsert, concatenating with the existing value.

NULL does not mean 'NO' value in SQL it means UNKNOWN value. sort of like the 'indeterminate' in math.

maybe you want a NOT NULL json value that you set to ''  or something when its empty.


--
john r pierce, recycling bits in santa cruz




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Have you tried using CASE?

INSERT INTO Fan  (oid, campaigns, facts)  VALUES (189,'{"campaign-id": "12345"}','{"attended": false}')
ON CONFLICT (oid)
DO UPDATE SET campaigns = EXCLUDED.campaigns,
              CASE WHEN fan.facts is NULL
                   THEN facts = EXCLUDED.facts
                   ELSE facts = fan.facts || EXCLUDED.facts
               END
    RETURNING *;

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

pgsql-general by date:

Previous
From: John R Pierce
Date:
Subject: Re: [GENERAL] Concatenating NULL with JSONB value return NULL
Next
From: Gavin Flower
Date:
Subject: Re: [GENERAL] About the MONEY type