Get records with empty hstore

Question

I'm using a PostgreSQL database with hstore extension and i'm trying to make a simplest thing possible - get all the records, which vd_data (hstore) column is empty ({}).

It sounds really easy but i'm not able to do it by myself nor find anything about it on the internet. I'm using it in my rails app so i'll post some examples using activerecord DSL:

Video.where('vd_data != NULL')
# => [] (empty result. I have about 20 videos with vd_data populated in my db)

Video.where('vd_data != {}')
# => Syntax error

Video.where('vd_data != ""')
# => ERROR:  zero-length delimited identifier at or near """"

Can anyone advise me on how such query should look in pure SQL ?

Answer 1

{} is not valid expression for HSTORE.

Instead, use expression hstore('') or even simply '' to represent empty HSTORE, like this:

Video.where("vd_data != ''")

Proof in SQLFiddle Demo.