Search code examples
sqlpostgresqlhstore

hstore: cannot concatenate two hstore values

New to Postgres, using v9.3, want to leverage hstore.

When I try to concatenate two hstore values I get a weird error:

SELECT p.properties->'name' || p.properties->'age' FROM people p where p.id=1;

The error is:

ERROR:  operator does not exist: text -> unknown
LINE 1: select n.properties->'name' || n.properties->'age' from n...
                                                   ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

I tried this too, and it didn't matter:

SELECT p.properties->'name'::text || p.properties->'age'::text FROM people p where p.id=1;

However, I can do 

SELECT p.properties->'name' FROM people p where p.id=1;

SELECT p.properties->'age' FROM people p where p.id=1;

Is it not possible to concatenate two hstore values out of the same hstore?

Any pointers appreciated!

Solution

  • You can do with the CAST function as is :

    SELECT CAST(p.properties->'name' AS text) || CAST(p.properties->'age' AS text) FROM people p where p.id=1;