Renaming hstore key in PostgreSQL 9.2
I think you're right that you have to pull the old pair out and put the new pair (with the renamed key) back in.
You could do it with a one-liner:
(h - from_key) || hstore(to_key, h -> from_key)
where h
is the hstore, from_key
is the key you want to change and to_key
is what you want to change it to. That will return a new hstore with the desired change but it assumes that from_key
is in h
; if from_key
isn't in h
then you'll end up with a to_key -> NULL
in your hstore. If you, like all sane people, don't want the stray NULL then I'd wrap the logic in a simple function to make it easier to add an existence check; something like this:
create or replace functionchange_hstore_key(h hstore, from_key text, to_key text) returns hstore as $$begin if h ? from_key then return (h - from_key) || hstore(to_key, h -> from_key); end if; return h;end$$ language plpgsql;
Then you can say both of these and get the expected results:
=> select change_hstore_key('a=>1,b=>2,c=>3'::hstore, 'b', 'pancakes'); change_hstore_key ------------------------------ "pancakes"=>"2", "a"=>"1", "c"=>"3"=> select change_hstore_key('a=>1,b=>2,c=>3'::hstore, 'pancakes', 'X'); change_hstore_key ------------------------------ "a"=>"1", "b"=>"2", "c"=>"3"