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?

1 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.

  • That is exactly what I meant, and the fix worked. Thank you. Commented Oct 6, 2014 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.