Robin Robin - 4 months ago 9
SQL Question

How to select a row from any hstore values?

I've a table

Content
in a PostgreSQL (9.5) database, which contains the column
title
. The
title
column is a
hstore
. It's a
hstore
, because the
title
is translated to different languages. For example:

example=# SELECT * FROM contents;
id | title | content | created_at | updated_at
----+---------------------------------------------+------------------------------------------------+----------------------------+----------------------------
1 | "de"=>"Beispielseite", "en"=>"Example page" | "de"=>"Beispielinhalt", "en"=>"Example conten" | 2016-07-17 09:20:23.159248 | 2016-07-17 09:20:23.159248
(1 row)


My question is, how can I select the
content
which
title
contains
Example page
?

SELECT * FROM contents WHERE title = 'Example page';


This query unfortunately doesn't work.

example=# SELECT * FROM contents WHERE title = 'Example page';
ERROR: Syntax error near 'p' at position 8
LINE 1: SELECT * FROM contents WHERE title = 'Example page';

Answer

The avals() function returns an array of all values in a hstore column. You can then match your value using any against that array:

select *
from contents 
where 'Example page' = any(avals(title))
Comments