Say you’re using the excellent Postgres JSON features. You may find that you get errors writing queries that use two tables – I often like to use “except” to diff datasets.
If you write a SQL query like the following, you can get an error:
with pre as ( select a, b from my_table ), post as ( select re->'a', re->'b' from log ) select * from pre except select * from post
While this looks like what you might want (the -> syntax looks intuitively correct), it uses the wrong operator, and you’ll get a type error:
EXCEPT types text and json cannot be matched
The answer is to use the ->> operator rather than ->. The “->” is equivalent to lodash _.pick, whereas ->> is like doing object[key].