Postgres SQL ERROR: EXCEPT types text and json cannot be matched

gary

5 years ago

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].