Thanks Tom, Melvin, and John!
@John, I keep forgetting the semantic differences between my programming
language and PostgreSQL.
I will go for Tom's COALESCE than Melvin's, purely for less typing.
Thanks again, all!
- Jong-won
On 19/12/16 11:46, Tom Lane wrote:
Melvin Davidson <melvin6925@xxxxxxxxx> writes:
On Sun, Dec 18, 2016 at 6:08 PM, John R Pierce <pierce@xxxxxxxxxxxx> 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.
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 *;
Another option is COALESCE:
...
DO UPDATE SET campaigns = EXCLUDED.campaigns,
facts = COALESCE(fan.facts, '{}'::jsonb) || EXCLUDED.facts
...
I'd argue though that if you think this is okay, then you're abusing
NULL; that's supposed to mean "unknown", not "known to be empty".
It would be better to initialize the column to '{}' to begin with.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
