Re: update without SET ? - Mailing list pgsql-sql
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.
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"}')unionselect 2::int as r,key,value from json_each_text('{"key":22,"header":44,"ident":66,"static03":"thatsit"}'))selectr,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 static03from jsonrowsgroup by r;alternative is I guess a simple function
Hector Vass07773 352 55901666 820 008MetaMetrics, International House, 107 Gloucester Road, Malmesbury, Wiltshire, SN16 0AJThis 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 2DRFrom: 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
