Re: update without SET ? - Mailing list pgsql-sql
| From | Michael Moore |
|---|---|
| Subject | Re: update without SET ? |
| Date | |
| Msg-id | CACpWLjMJvfU5WYFPiGdEEGmtGnAAj0D_S2OvGd4yvNYhLS=Otg@mail.gmail.com Whole thread Raw |
| In response to | Re: update without SET ? ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Responses |
Re: update without SET ?
("David G. Johnston" <david.g.johnston@gmail.com>)
|
| List | pgsql-sql |
using 9.5
Also, tried "src.key" and that does not work either, and tried about a dozen permutation none of which worked. src.src.key gave some interesting results if I recall.
Also, was just thinking that two columns with names of say: attrib and att would cause a problem if att was NOT in the JSON and attrib was in, because: 'att' ? 'attrib' = true. I should be able to clean this up if indeed it is a problem at all. Have not tested it yet.
Also, tried "src.key" and that does not work either, and tried about a dozen permutation none of which worked. src.src.key gave some interesting results if I recall.
Also, was just thinking that two columns with names of say: attrib and att would cause a problem if att was NOT in the JSON and attrib was in, because: 'att' ? 'attrib' = true. I should be able to clean this up if indeed it is a problem at all. Have not tested it yet.
Thanks for all your help David, not only are you solving my problem, I'm learning a lot.
Mike
On Wed, Feb 3, 2016 at 5:44 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
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@gmail.com> wrote:Hi David.This gives ...ERROR: column src.key does not existLINE 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.MikeOn Wed, Feb 3, 2016 at 3:35 PM, David G. Johnston <david.g.johnston@gmail.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 mytestSELECT * 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 mytestSET 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) srcWHERE mytest.key = src.key;UPDATE mytestSET header = CASE WHEN source_json ? 'header' THEN src.header ELSE mytest.header ENDFROM (SELECT *FROM (VALUES ('{"key":22,"header":44,"ident":null,"static02":"that"}'::jsonb)) src_json (source_json),LATERAL jsonb_populate_record(null::mytest, source_json)) srcWHERE 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.