Removing a Key From HSTORE in Postgresql

Question

At the moment I have an hstore that describes the properties of various items. When searching I need to remove values that are unique to a each item (such as serial number, vin, and so on). According to the postgresql docs I should be able to remove a key from an hstore with the following operator

As per the postgres docs hstore - text delete key from left operand 'a=>1, b=>2, c=>3'::hstore - 'b'::text

In my code I have

IF item.details ? 'vin' THEN item.details::hstore - 'vin'::TEXT; END IF;

When I go to create the function I get the syntax error

'syntax error at or near "::"'

When I remove the type declarations from the end of the values I get the error

'syntax error at or near "-"'

As far as I can see this conforms to how the postgres docs say the operator should work at http://www.postgresql.org/docs/9.3/static/hstore.html . Is there something I am missing?

Answer 1

You're trying to use the expression item.details::hstore - 'vin'::TEXT as a PL/PgSQL statement.

Where's the result supposed to go?

At a guess, I think what you intended to write is:

item.details := item.details - 'vin'::TEXT;

i.e. "set item.details to the old value of item.details with the key vin removed".

It's kind of hard to be sure without more context.