Thread: update without SET ?

update without SET ?

From

Michael Moore

Date:

03 February 2016, 21:49:28

I want to UPDATE a table but I will not know until run time which columns will be updated. I would like to do something like this:

update mytest t (SELECT * FROM json_populate_record(null::mytest,'{"key":22,"header":44,"ident":66,"static01":"this","static02":"that"}'));

In other words, I will be receiving a json document on an input parameter. The columns named in the json document can be a sub set of those that exist in the table.

tia Mike

Re: update without SET ?

From

Hector Vass

Date:

03 February 2016, 23:59:49

json is really just key-value pairs..

why not take the same approach and store the data in a temp table as key-value then have a static sumif

with jsonrows as(

select 1::int as r,key,value from json_each_text('{"key":22,"header":44,"ident":66,"static01":"this","static02":"that"}')

union

select 2::int as r,key,value from json_each_text('{"key":22,"header":44,"ident":66,"static03":"thatsit"}')

)

select

max(case when key='key' then value::int else null end) as key,

max(case when key='header' then value::int else null end) as header,

max(case when key='ident' then value::int else null end) as ident,

max(case when key='static01' then value::varchar(200) else null end) as static01,

max(case when key='static02' then value::varchar(200) else null end) as static02,

max(case when key='static03' then value::varchar(200) else null end) as static03

from jsonrows

group by r

;

alternative is I guess a simple function

Hector Vass

07773 352 559

01666 820 008

MetaMetrics, International House, 107 Gloucester Road, Malmesbury, Wiltshire, SN16 0AJ

This e-mail and any attachments are confidential and for the attention of the addressee only. If you are not the intended recipient, any use, disclosure or copying of this document is unauthorised. If you have received this document in error please immediately notify the sender and delete this e-mail from your system. Whilst all emails sent by MetaMetrics are scanned using up-to-date virus scanning software, MetaMetrics Ltd. accepts no liability for any loss or damage which may be caused by software viruses and recommend that you conduct your own virus checks on all attached materials. Please note that any attached materials remain the exclusive property of MetaMetrics Ltd. unless expressly stated otherwise. Metametrics Limited is a limited company registered in England & Wales. Registered number 05453613. Registered offices at 86 Shirehampton Road, Stoke Bishop, Bristol, BS9 2DR

From: pgsql-sql-owner@postgresql.org <pgsql-sql-owner@postgresql.org> on behalf of Michael Moore <michaeljmoore@gmail.com>
Sent: 03 February 2016 18:49
To: postgres list
Subject: [SQL] update without SET ?

I want to UPDATE a table but I will not know until run time which columns will be updated. I would like to do something like this:

update mytest t (SELECT * FROM json_populate_record(null::mytest,'{"key":22,"header":44,"ident":66,"static01":"this","static02":"that"}'));

In other words, I will be receiving a json document on an input parameter. The columns named in the json document can be a sub set of those that exist in the table.

tia Mike

Re: update without SET ?

From

"David G. Johnston"

Date:

04 February 2016, 00:51:19

On Wed, Feb 3, 2016 at 11:49 AM, Michael Moore <michaeljmoore@gmail.com> wrote:

I want to UPDATE a table but I will not know until run time which columns will be updated. I would like to do something like this:
update mytest t (SELECT * FROM json_populate_record(null::mytest,'{"key":22,"header":44,"ident":66,"static01":"this","static02":"that"}'));

In other words, I will be receiving a json document on an input parameter. The columns named in the json document can be a sub set of those that exist in the table.

If a key is defined but has a value of null what do you want to do?

not fully tested...

UPDATE mytest

SET col1 = COALESCE(src.col1, mytest.col1)

FROM (SELECT * FROM json_populate_record(...)) src;

json_populate_record outputs the full structure of mytest regardless of its input so simply match every column and use COALESCE to choose between the value in the json and the value already on the table.

Aside from the null json value issue that should work - if not you probably need to describe your problem in more detail.

David J.

Re: update without SET ?

From

Michael Moore

Date:

04 February 2016, 01:09:40

Hector,

I think I was not clear. My function will be called from JDBC. The input parameter will be a JSON document which will contain data for a single row in a table. If the row does not exist I will INSERT it. ( I've got no problem with the insert.) If the record already exists, I will update it. There are 240 columns in the table. The selection of columns which must be updated is random. So, there are 3 update possibilities for each table column.

1) the column/key is named in the JSON document and the VALUE is not null. Action: Simply update the table column with the new value.
2) the column/key is named in the JSON document and the VALUE is NULL. Action: update the table column with a null value.

3) the column/key is not named in JSON document. Action: retain the existing value in the db record (do not nullify it)

So, it is critical that I distinguish between an EXISTING key with a value of NULL and a non-Existing key.

unfortunately this:

select string_agg(key||'='||value,',') as mycol from json_each_text('{"key":22,"header":44,"ident":66,"static01":null,"static02":"that"}') ;

and this:

select string_agg(key||'='||value,',') as mycol from json_each_text('{"key":22,"header":44,"ident":66,"static02":"that"}') ;

The result of both of the above queries:

"key=22,header=44,ident=66,static02=that"

I have no way to know if I should set static01 to null or leave it as is.

Thanks!

Mike

On Wed, Feb 3, 2016 at 12:59 PM, Hector Vass <hector.vass@metametrics.co.uk> wrote:

json is really just key-value pairs..
why not take the same approach and store the data in a temp table as key-value then have a static sumif

with jsonrows as(
select 1::int as r,key,value from json_each_text('{"key":22,"header":44,"ident":66,"static01":"this","static02":"that"}')
union
select 2::int as r,key,value from json_each_text('{"key":22,"header":44,"ident":66,"static03":"thatsit"}')
)
select
r,
max(case when key='key' then value::int else null end) as key,
max(case when key='header' then value::int else null end) as header,
max(case when key='ident' then value::int else null end) as ident,
max(case when key='static01' then value::varchar(200) else null end) as static01,
max(case when key='static02' then value::varchar(200) else null end) as static02,
max(case when key='static03' then value::varchar(200) else null end) as static03
from jsonrows
group by r
;

alternative is I guess a simple function

Hector Vass
07773 352 559
01666 820 008

MetaMetrics, International House, 107 Gloucester Road, Malmesbury, Wiltshire, SN16 0AJ

This e-mail and any attachments are confidential and for the attention of the addressee only. If you are not the intended recipient, any use, disclosure or copying of this document is unauthorised. If you have received this document in error please immediately notify the sender and delete this e-mail from your system. Whilst all emails sent by MetaMetrics are scanned using up-to-date virus scanning software, MetaMetrics Ltd. accepts no liability for any loss or damage which may be caused by software viruses and recommend that you conduct your own virus checks on all attached materials. Please note that any attached materials remain the exclusive property of MetaMetrics Ltd. unless expressly stated otherwise. Metametrics Limited is a limited company registered in England & Wales. Registered number 05453613. Registered offices at 86 Shirehampton Road, Stoke Bishop, Bristol, BS9 2DR

From: pgsql-sql-owner@postgresql.org <pgsql-sql-owner@postgresql.org> on behalf of Michael Moore <michaeljmoore@gmail.com>
Sent: 03 February 2016 18:49
To: postgres list
Subject: [SQL] update without SET ?

I want to UPDATE a table but I will not know until run time which columns will be updated. I would like to do something like this:
update mytest t (SELECT * FROM json_populate_record(null::mytest,'{"key":22,"header":44,"ident":66,"static01":"this","static02":"that"}'));

In other words, I will be receiving a json document on an input parameter. The columns named in the json document can be a sub set of those that exist in the table.

tia Mike

Re: update without SET ?

From

"David G. Johnston"

Date:

04 February 2016, 01:20:32

On Wed, Feb 3, 2016 at 2:51 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Wed, Feb 3, 2016 at 11:49 AM, Michael Moore <michaeljmoore@gmail.com> wrote:
I want to UPDATE a table but I will not know until run time which columns will be updated. I would like to do something like this:
update mytest t (SELECT * FROM json_populate_record(null::mytest,'{"key":22,"header":44,"ident":66,"static01":"this","static02":"that"}'));

In other words, I will be receiving a json document on an input parameter. The columns named in the json document can be a sub set of those that exist in the table.

If a key is defined but has a value of null what do you want to do?

not fully tested...

UPDATE mytest
SET col1 = COALESCE(src.col1, mytest.col1)
FROM (SELECT * FROM json_populate_record(...)) src;

json_populate_record outputs the full structure of mytest regardless of its input so simply match every column and use COALESCE to choose between the value in the json and the value already on the table.

Aside from the null json value issue that should work - if not you probably need to describe your problem in more detail.

Modification thought to deal with JSON null values on existing keys:

UPDATE mytest

SET col1 = (CASE WHEN src.json_value ? 'col1' THEN src.col1 ELSE mytest.col1 END)

FROM (

SELECT *

FROM json_populate_record(json_value)

CROSS JOIN

SELECT json_value AS source_json

) src WHERE mytest.key = src.key;

David J.

Re: update without SET ?

From

Michael Moore

Date:

04 February 2016, 02:09:23

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;

On Wed, Feb 3, 2016 at 2:20 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Wed, Feb 3, 2016 at 2:51 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Feb 3, 2016 at 11:49 AM, Michael Moore <michaeljmoore@gmail.com> wrote:
I want to UPDATE a table but I will not know until run time which columns will be updated. I would like to do something like this:
update mytest t (SELECT * FROM json_populate_record(null::mytest,'{"key":22,"header":44,"ident":66,"static01":"this","static02":"that"}'));

In other words, I will be receiving a json document on an input parameter. The columns named in the json document can be a sub set of those that exist in the table.

If a key is defined but has a value of null what do you want to do?

not fully tested...

UPDATE mytest
SET col1 = COALESCE(src.col1, mytest.col1)
FROM (SELECT * FROM json_populate_record(...)) src;

json_populate_record outputs the full structure of mytest regardless of its input so simply match every column and use COALESCE to choose between the value in the json and the value already on the table.

Aside from the null json value issue that should work - if not you probably need to describe your problem in more detail.

Modification thought to deal with JSON null values on existing keys:

UPDATE mytest
SET col1 = (CASE WHEN src.json_value ? 'col1' THEN src.col1 ELSE mytest.col1 END)
FROM (
SELECT *
FROM json_populate_record(json_value)
CROSS JOIN
SELECT json_value AS source_json
) src WHERE mytest.key = src.key;

David J.

Re: update without SET ?

From

"David G. Johnston"

Date:

04 February 2016, 02:35:17

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.

Re: update without SET ?

From

Michael Moore

Date:

04 February 2016, 03:49:34

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.

Re: update without SET ?

From

"David G. Johnston"

Date:

04 February 2016, 04:44:34

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.

Re: update without SET ?

From

Michael Moore

Date:

04 February 2016, 19:19:43

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.

Re: update without SET ?

From

"David G. Johnston"

Date:

04 February 2016, 19:31:03

On Thu, Feb 4, 2016 at 9:19 AM, Michael Moore <michaeljmoore@gmail.com> wrote:

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.

Just re-ran the query I provided on the table

mytest with inserted record you provided above and it worked fine. Query executed using psql.

But I have no clue what you mean by: 'att' ? 'attrib' = true; SELECT 'att' ? 'attrib' doesn't even execute and the existence operator only considers exact matches.

This will work: if you get more errors you will need to supply the complete test case.

David J.

P.S. List convention is to either inline or bottom-post.

Re: update without SET ?

From

Michael Moore

Date:

04 February 2016, 22:19:31

Just re-ran the query I provided on the table

mytest with inserted record you provided above and it worked fine. Query executed using psql.

You are correct, no problem using psql, it must have been a pgAdmin III problem.

But I have no clue what you mean by: 'att' ? 'attrib' = true; SELECT 'att' ? 'attrib' doesn't even execute and the existence operator only considers exact matches.

I have now leaned what the ? operator does. So, no problem here.

P.S. List convention is to either inline or bottom-post.

I hope this is correct.

Again, thanks so much, have a great day.

Mike

On Thu, Feb 4, 2016 at 8:31 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Thu, Feb 4, 2016 at 9:19 AM, Michael Moore <michaeljmoore@gmail.com> wrote:

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.

Just re-ran the query I provided on the table

mytest with inserted record you provided above and it worked fine. Query executed using psql.

But I have no clue what you mean by: 'att' ? 'attrib' = true; SELECT 'att' ? 'attrib' doesn't even execute and the existence operator only considers exact matches.

This will work: if you get more errors you will need to supply the complete test case.

David J.

P.S. List convention is to either inline or bottom-post.