Search code examples
postgresqlcastinghstore

Unexpected end of string when removing key from hstore

When I am removing a key from a HSTORE, I get the error 'Unexpected end of string':

DB=# UPDATE mytable SET properties = properties - 'key'  where label = '9912345678';
ERROR:  Unexpected end of string
LINE 1: UPDATE mytable SET properties = properties - 'key'  where ...

When I explicitly cast that string, it does work:

DB=# UPDATE mytable SET properties = properties - 'key'::text  where label = '9912345678';
UPDATE 1

Why does it give this error message? Isn't 'key' a TEXT column? Or at least a string with a expected end?

Solution

  • The operator is overloaded so the right-hand operand should be explicitly cast. Note the example usage for text, text[] and hstore in the documentation:

    'a=>1, b=>2, c=>3'::hstore - 'b'::text
'a=>1, b=>2, c=>3'::hstore - ARRAY['a','b']
'a=>1, b=>2, c=>3'::hstore - 'a=>4, b=>2'::hstore

    In the statement

    UPDATE mytable SET properties = properties - 'key' where label = '9912345678';

    the string 'key' may be resolved as text or hstore. The first choice of the hstore algorithm is hstore, so the statement is resolved to

    UPDATE mytable SET properties = properties - 'key'::hstore where label = '9912345678';

    and raises the error

    ERROR:  Unexpected end of string

    though the error message could be more informative, like Unexpected end of string while parsing hstore constant.