From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Michael Moore <michaeljmoore(at)gmail(dot)com> |
Cc: | postgres list <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: update without SET ? |
Date: | 2016-02-03 23:35:12 |
Message-ID: | CAKFQuwasLLRfC8pckaFnUQarCpwsTP6mXzpkV99VuxuEgh82yg@mail.gmail.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Wed, Feb 3, 2016 at 4:09 PM, Michael Moore <michaeljmoore(at)gmail(dot)com>
wrote:
> Lost me here David. I can't seem to bridge the gap between your
> pseudo-code and an actual example.
>
> setup:
> CREATE TABLE mytest
> (key bigint NOT NULL,
> header bigint NOT NULL,
> ident bigint NOT NULL,
> static01 character varying(100),
> static02 character varying(220) );
>
> INSERT into mytest
> SELECT * FROM
> json_populate_record(null::mytest,'{"key":22,"header":44,"ident":66,"static01":"this","static02":"that"}');
>
> And here is my failed interpretation of your example: *(I used inline
> JSON string just for ease. In real life it will be a variable of type
> JSON.)*
>
> UPDATE mytest
> SET header = (CASE WHEN src.header = 'header' THEN src.header ELSE
> mytest.header END)
> FROM (
> SELECT * FROM json_populate_record(null::mytest,
> '{"key":22,"header":44,"ident":null,"static02":"that"}')
> CROSS JOIN
> (SELECT '{"key":22,"header":44,"ident":null,"static02":"that"}') AS
> source_json
> ) src
> WHERE mytest.key = src.key;
>
>
UPDATE mytest
SET header = CASE WHEN source_json ? 'header' THEN src.header ELSE
mytest.header END
FROM (
SELECT *
FROM (VALUES
('{"key":22,"header":44,"ident":null,"static02":"that"}'::jsonb)) src_json
(source_json),
LATERAL jsonb_populate_record(null::mytest, source_json)
) src
WHERE mytest.key = src.key
;
LATERAL makes writing this a bit cleaner but is not mandatory. The CROSS
JOIN would have worked but didn't feel like playing with the syntax.
I am not sure where you got the idea to try " WHEN src.header = 'header'"
especially since the value of src.header is 44...
Apparently the exists operator (no matching function so if you cannot use
"?" as an operator you will have issues...) is jsonb only.
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Moore | 2016-02-04 00:49:28 | Re: update without SET ? |
Previous Message | Michael Moore | 2016-02-03 23:09:18 | Re: update without SET ? |