Request a Demo
Home   >   mailing lists

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
In response to Re: update without SET ?  ("David G. Johnston" <david.g.johnston@gmail.com>)
Responses Re: update without SET ?
List pgsql-sql
Tree view
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.

 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 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.g.johnston@gmail.com> wrote:
On Wed, Feb 3, 2016 at 4:09 PM, Michael Moore <michaeljmoore@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 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.
​



pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: [GENERAL] Q: documentation bug ?
Next
From: "David G. Johnston"
Date:
Subject: Re: update without SET ?