According to TFM (Postgres docs), you use the normal concatenation operator to join two HSTOREs:
SELECT 'a=>b, c=>d'::hstore || 'c=>x, d=>q'::hstore
Result:
"a"=>"b", "c"=>"x", "d"=>"q"
However, I'm getting an error when running that exact same command:
[42883] ERROR: operator does not exist: hstore || hstore Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
The only way I can get the desired result is to do something so hackish it makes me want to cry: converting any HSTOREs I have into text first, then concatenating the text and converting back to an HSTORE. This, of course, isn't good as the docs also state that if there are duplicate keys, there's no guarantee as to which one will survive the concatenation.
Before I file a bug with the Postgres folks, can anybody else duplicate this? Version info:
select version();
PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit
select extname,extversion from pg_catalog.pg_extension;
hstore 1.3
Figured out the issue. The HSTORE extension was installed into a separate schema; I was able to call that schema by identifying it (sys.hstore), but it was still not liking the operator ||. The fix was actually pretty simple: I added sys to the search_path.