Renaming hstore key in PostgreSQL 9.2

Question

I've been evaluating PostgreSQL's hstore functionality (9.2) and the only thing the fine manual isn't explicit about is how to rename keys. For instance, how could I rename the key c to ai_count?

"c"=>"3", "ai_voltage"=>"3", "ai_temperature"=>"28"

I'm thinking there is no direct way to do this and that it involves duplicating the c key to a ai_count key, then dropping the c key. How can I do this, ideally as a one-liner which can be applied to multiple records?

Answer 1

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 function
change_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"