9

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 ?

mbajur
  • 4,406
  • 5
  • 49
  • 79

1 Answers1

9

{} 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.

mvp
  • 111,019
  • 13
  • 122
  • 148
  • Ohhh, now i see. I was trying `Video.where('vd_data != ""')` and it was giving me a syntax error so i left that track behing. Great, thank you! – mbajur May 12 '14 at 10:01
  • 2
    Unlike MySQL or SQLite, in PostgreSQL there is big difference in using single vs double quotes. To represent string values, you must always use single quotes. To escape identifier names, you must use double quotes. – mvp May 12 '14 at 10:49