CODEWITHSUNDEEP
sqlpostgresqlhstore
Robin
Robin

Reputation: 8518

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';

Upvotes: 3

Views: 1215

Answers (2)

user330315
user330315

Reputation:

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))

Upvotes: 4

Mohamed
Mohamed

Reputation: 822

You should use like in where clause 

SELECT * FROM contents WHERE title like '%Example page%';

Hope it helps you.

Upvotes: 0

Related Questions