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-04 01:44:29 |
Message-ID: | CAKFQuwbsA8K7mjWiK6FxjmkZcY0N500GQvsAUEgwPKF5B8QZbg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
What version? I am reasonably certain it ran as copied on 9.5 but maybe a
pasto.
Btw: src.key is not the same as "src.key" - the former is column key on
relation src while the later is the in-scope column named "src.key"
David J.
On Wednesday, February 3, 2016, Michael Moore <michaeljmoore(at)gmail(dot)com>
wrote:
> Hi David.
> This gives ...
> ERROR: column src.key does not exist
> LINE 8: WHERE mt.key = src.key;
> ^
> HINT: Perhaps you meant to reference the column "src.key".
> ********** Error **********
> Funny how it is suggesting the exact thing that it says is the problem. In
> reality, I will have the key value in a variable so I can do ...
> WHERE mytest.key = key_variable;
>
> There are a few techniques you are using here that I am not familiar with.
> I will study-up and get back to you later.
>
> Mike
>
>
>
> On Wed, Feb 3, 2016 at 3:35 PM, David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com
> <javascript:_e(%7B%7D,'cvml','david(dot)g(dot)johnston(at)gmail(dot)com');>> wrote:
>
>> On Wed, Feb 3, 2016 at 4:09 PM, Michael Moore <michaeljmoore(at)gmail(dot)com
>> <javascript:_e(%7B%7D,'cvml','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 | Shashank Dutt Jha | 2016-02-04 07:23:01 | Create db fails using utility tool |
Previous Message | Michael Moore | 2016-02-04 00:49:28 | Re: update without SET ? |