On 11st of December 2020, Tom Lane committed patch:
Allow subscripting of hstore values. This is basically a finger exercise to prove that it's possible for an extension module to add subscripting ability. Subscripted fetch from an hstore is not different from the existing "hstore -> text" operator. Subscripted update does seem to be a little easier to use than the traditional update method using hstore concatenation, but it's not a fundamentally new ability. However, there may be some value in the code as sample code, since it shows what's basically the minimum-complexity way to implement subscripting when one needn't consider nested container objects. Discussion: https://postgr.es/m/3724341.1607551174@sss.pgh.pa.us
This is really cool. While this patch adds actual functionality, the backbone of this commit was actually this commit from two days earlier.
The general idea is that now, we will be able to provide subscripting ( value[subscript] ) for any datatype, not only arrays.
Hstore patch added support of subscripting to hstore. Which means that I can now:
$ SELECT payload['a'], payload FROM z WHERE id = 1 \gx ─[ RECORD 1 ]───────────────────────────────────────────────────────────────────────────────────────────────────────── payload │ 47 payload │ "a"=>"47", "b"=>"35", "d"=>"97", "f"=>"42", "i"=>"75", "m"=>"35", "p"=>"41", "q"=>"86", "r"=>"56", "t"=>"60"
Of course, I could previously use custom operator:
$ SELECT payload->'a', payload FROM z WHERE id = 1 \gx ─[ RECORD 1 ]────────────────────────────────────────────────────────────────────────────────────────────────────────── ?COLUMN? │ 47 payload │ "a"=>"47", "b"=>"35", "d"=>"97", "f"=>"42", "i"=>"75", "m"=>"35", "p"=>"41", "q"=>"86", "r"=>"56", "t"=>"60"
which, of course, it still available, but now we can also use [ subscript ].
One thing I need to caution you about is that while you can use it for selecting, using it for “where" clause is not indexable (yet?):
$ EXPLAIN analyze SELECT * FROM z WHERE payload['a'] = '47'; QUERY PLAN ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────── Gather (cost=1000.00..5778.67 ROWS=1000 width=117) (actual TIME=1.538..15.274 ROWS=734 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan ON z (cost=0.00..4678.67 ROWS=417 width=117) (actual TIME=0.025..10.223 ROWS=245 loops=3) FILTER: (payload['a'::text] = '47'::text) ROWS Removed BY FILTER: 66422 Planning TIME: 0.118 ms Execution TIME: 15.326 ms (8 ROWS)
While you can use index when searching using @> operator:
$ EXPLAIN analyze SELECT * FROM z WHERE payload @> 'a=>47'::hstore; QUERY PLAN ────────────────────────────────────────────────────────────────────────────────────────────────────────────────── Bitmap Heap Scan ON z (cost=56.16..131.96 ROWS=20 width=117) (actual TIME=5.975..9.452 ROWS=734 loops=1) Recheck Cond: (payload @> '"a"=>"47"'::hstore) ROWS Removed BY INDEX Recheck: 6586 Heap Blocks: exact=3139 -> Bitmap INDEX Scan ON hidx (cost=0.00..56.15 ROWS=20 width=0) (actual TIME=5.719..5.719 ROWS=7320 loops=1) INDEX Cond: (payload @> '"a"=>"47"'::hstore) Planning TIME: 0.200 ms Execution TIME: 9.517 ms (8 ROWS)
All in all, I find it great feature, and am expecting more datatypes to support this feature, starting, probably, with json.
Thanks to all involved.