0
SELECT '{}'::json = '{}'::json

Will fail with error.

Is JSON the only data type that postgres will fail to compare on equality out of the box? If not then how to work out which data types cannot be compared (against the same data type) on equality?

I am only interested in getting a list of data types that will fail an equality comparison.

user5480949
  • 1,410
  • 1
  • 15
  • 22

1 Answers1

2

To find out which types support the = operator, you can use the following query:

select lt.typname as left_type, 
       rt.typname as right_type
from pg_operator opr
  join pg_type lt on lt.oid = opr.oprleft
  join pg_type rt on rt.oid = opr.oprright
where oprname = '=';

By using a NOT EXISTS condition, you can find out all types that do not support =

select t.typname
from pg_type t
where not exists (select *
                  from pg_operator opr
                  where opr.oprname = '='
                    and opr.oprleft = t.oid 
                    and opr.oprright = t.oid);