Re: update without SET ?
От | David G. Johnston |
---|---|
Тема | Re: update without SET ? |
Дата | |
Msg-id | CAKFQuwbsA8K7mjWiK6FxjmkZcY0N500GQvsAUEgwPKF5B8QZbg@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: update without SET ? (Michael Moore <michaeljmoore@gmail.com>) |
Ответы |
Re: update without SET ?
(Michael Moore <michaeljmoore@gmail.com>)
|
Список | 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.
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.
В списке pgsql-sql по дате отправления: