CODEWITHSUNDEEP
postgresqlhstore
Bart Friederichs
Bart Friederichs

Reputation: 33533

hstore operator precedence changed in between versions

I found that this query:

SELECT properties->'somekey' IS NOT DISTINCT FROM 'someValue' FROM myTable;

does work in PostgreSQL 9.5, but not in 9.4, it gives this error:

ERROR: operator does not exist: hstore -> boolean

So, apprently the operator precedende of the -> operator changed between versions. Thing is, I cannot find this (or even what the precedence should be) in the docs.

Can anybody shed some light on this?

Upvotes: 2

Views: 175

Answers (1)

A Doe
A Doe

Reputation: 31

I was puzzled briefly by this same error message. The issue is that Postgres first evaluates 'somekey' IS NOT DISTINCT FROM 'somevalue' and then uses the boolean produced from that statement as the key to find within properties, thus producing the error. You simply need to change

SELECT properties->'somekey' IS NOT DISTINCT FROM 'someValue' FROM myTable;

to

SELECT (properties->'somekey') IS NOT DISTINCT FROM 'someValue' FROM myTable;

so that the code evaluates acccording to "the SQL standard".

Upvotes: 1

Related Questions