Concatenating NULL with JSONB value return NULL
PostgreSQL 9.6.1
Hi
I have a NULL-able JSONB type column and want to perform upsert,
concatenating with the existing value.
The query looks like (campaigns and facts columns are JSONB type, in the
below) :
INSERT INTO Fan (oid, campaigns, facts) VALUES (189,'{"campaign-id":
"12345"}','{"attended": false}') ON CONFLICT (oid)
DO UPDATE SET campaigns = EXCLUDED.campaigns, facts = fan.facts ||
EXCLUDED.facts RETURNING *;
And this does not work when the existing JSONB type column has NULL
value. For example:
select NULL::JSONB || '{"A": "b"}'::JSONB;
I would expect the above returns '{"A": "b"}', but PostgreSQL does not
work as I expected.
What's the best way to make concatenating with NULL returns the
right-hand side value?
(One way I can think of is, giving a default value of '{}' instead of
NULL, but I'd like to know any alternatives)
Thanks
- Jong-won
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
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.
Melvin Davidson <melvin6925@gmail.com> writes:
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.
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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@gmail.com> writes:
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.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general