Re: [GENERAL] Concatenating NULL with JSONB value return NULL

Поиск
Список
Период
Сортировка
От Melvin Davidson
Тема Re: [GENERAL] Concatenating NULL with JSONB value return NULL
Дата
Msg-id CANu8FiwcGP9NMtGqoTszT+r6hgXS0J1C7zLmvREO_ie7ExEGWw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [GENERAL] Concatenating NULL with JSONB value return NULL  (John R Pierce <pierce@hogranch.com>)
Ответы Re: [GENERAL] Concatenating NULL with JSONB value return NULL
Список pgsql-general
Дерево обсуждения


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 по дате отправления:

Предыдущее
От: John R Pierce
Дата:
Сообщение: Re: [GENERAL] Concatenating NULL with JSONB value return NULL
Следующее
От: Gavin Flower
Дата:
Сообщение: Re: [GENERAL] About the MONEY type