Postgresql HStore “NOT” query how-to

Yes, you read that right.
No, I am not dyslexic.
Here are a few snippets that demonstrate how to query for items that exist (or do not exist) in a postgres hstore (in this example, a table called document that contains a primary key “id” , a varchar “name” and a hstore “status”

select id, name, status from document WHERE status->'CONTAINER' ='1';
 select id, name, status from document where not status ?'CONTAINER'='1';

-- equivalent to the following

select id, name, status from document WHERE status OPERATOR(public.->) 'CONTAINER' ='1';
 select id, name, status from document where not status OPERATOR(public.?) 'CONTAINER'='1';

Additionally, it bears mentioning that if you try to use the above queries in their current form in a JDBC type environment, you will find that they will fail (as the ? character conflicts with the JDBC ? placeholder).

Here’s one JDBC compliant workaround I know , if merely checking for, say, the absence of the ‘CONTAINER’ key in the hstore. It uses the ‘exist’ function which is the verbose form the the ? operator.

 select id, name, status from document where not public.exist(status, 'CONTAINER') ;
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s