From: | Michael Moore <michaeljmoore(at)gmail(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | postgres list <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: update without SET ? |
Date: | 2016-02-04 00:49:28 |
Message-ID: | CACpWLjPsd4ODSG91_5LMTJyUe604=WWAqz+zU6zHjCAxbyPitA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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> wrote:
> 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 | David G. Johnston | 2016-02-04 01:44:29 | Re: update without SET ? |
Previous Message | David G. Johnston | 2016-02-03 23:35:12 | Re: update without SET ? |