atomically increment in PostgreSQL hstore like $inc in MongoDB

Question

New pg hstore looks awesome

http://www.postgresql.org/docs/devel/static/hstore.html

but it seems do not support atomically increment like MongoDB?

db.mycoll.update({mykey: myval}, {my_counter: {$inc: 1}})

How to do this with PostgreSQL Hstore?

Answer 1

MongoDB needs a $inc operator because:

1. Atomic operations in MongoDB are difficult without specific low-level support.
2. The interface isn't rich enough to express c = c + 1 without a special operator.

You just need to express c = c + 1 with hstores. This task is complicated a little because hstores use strings for both the keys and values and that leaves you with a mess of casting. I think you're stuck with something nasty like this:

update t
set h = h || hstore('my_counter', ((h -> 'my_counter')::integer + 1)::text)
where mykey = myval

The (h -> 'my_counter')::integer + 1 does the increment by extracting the value (h -> 'my_counter'), casting it to an integer, and adding one to it. Then you build a single element hstore with hstore('my_counter', ...) and an explicit ::text cast on the value to make sure PostgreSQL knows which function you want. Finally, you concatenate the new key-value onto the original hstore with h || hstore(...) to replace the old value.

If you don't want to use that somewhat nasty mess all the time then you could wrap it a simple function and say:

update t
set h = hstore_inc(h, 'my_counter', 1)
where ...

to hide the nastiness.

I'm sure there are other ways to do it (using the various to/from array functions perhaps) but the above should work.