Re: update without SET ? - Mailing list 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.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.

​