Search code examples
postgresqlhstore

PostgreSQL - Query on hstore - column does not exists

I wonder if someone could have an idea what is going wrong with this simple query on a hstore column in PostgreSQL 9.2

The queries are runned in pgAdmin

select attributeValue->"CODE_MUN" from shapefile_feature

returns: « attributevalue » column does not exists

when doing:

select * from shapefile_feature;

all the columns are returned including attributeValue, the hstore column

what is the problem?

Solution

  • PostgreSQL distinguish between "identifiers" and 'literal'. Identifiers are schema, table, column's, .. names, literals are others. A attribute in hstore are not SQL identifiers. So you have to pass their names as literals. Operator "->" is only shortcut for function "fetchval(hstore, text)" with possibility be indexed.

    select attributeValue->'CODE_MUN' from shapefile_feature

    is internally transformed to (don't do this transformation by self!)

    select fetchval(attributeValue, 'CODE_MUN') from shapefile_feature

    on buggy example in transformed form, you can better understand to error message:

    select fetchval(attributeValue, "CODE_MUN") from shapefile_feature

    PostgreSQL tries to find column "CODE_MUN" in shapefile_feature, bacause used double quotes means identifiers (in case sensitive notation).