Substract 2 hstore, You might need to add explicit type casts

Question

Documentation for postgres has hstore - hstore which delete matching pairs from left operand. Installed postgres extensions.

When I try

select public.hstore('"x"=>"30", "y"=>"c"') - 
       public.hstore('"x"=>"30","y"=>"fred"')

is error-ing with following

ERROR:  operator does not exist: public.hstore - public.hstore
LINE 3:  select public.hstore('"x"=>"30", "y"=>"c"') - public.hstore...
                                                     ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

try this one 'x=>30, y=>"c"'::hstore - 'x=>30, y=>"fred"'::hstore — Roman Pekar
if i do not prepend public it does not find the hstore, if I run select 'x=>30, y=>"c"'::public.hstore - 'x=>30, y=>"fred"'::public.hstore then complains about operator — user2624443

Craig Ringer Craig Ringer · Accepted Answer · 2013-09-05T13:54:09

You've installed the hstore extension into the public schema, which is not on your search_path. To find the types you're schema-qualifying them but you're not qualifying the - operator that works on those types.

This means that hstore's operator definitions will not be found. You must:

Schema-qualify the operator using OPERATOR(public.-);
Put public on the search_path; or
Uninstall hstore and then install it into its own dedicated schema that is on the search_path.

An example of schema-qualified operator syntax is:

SELECT 1 OPERATOR(pg_catalog.-) 2;

Substract 2 hstore, You might need to add explicit type casts

2 Answers