CODEWITHSUNDEEP
postgresqlpostpostgresql-9.1psql
user2624443
user2624443

Reputation: 60

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

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.

Upvotes: 1

Views: 3840

Answers (2)

Vince
Vince

Reputation: 787

I ran into this issue as well, nothing worked for me from my code (worked via pgadmin - query). Eventually after looking at the functions within public I found this which worked for me. 

public.fetchval(hstore, key)

Upvotes: 0

Craig Ringer
Craig Ringer

Reputation: 324751

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;

Upvotes: 6

Related Questions