Re: comparison operators

David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> Andrew Dunstan wrote
>> I think I'd rather just say "for many data types" or something along
>> those lines, rather than imply that there is some obvious rule that
>> users should be able to intuit.

> Ideal world for me: we'd list the data types that do not provide comparison
> operators (or not a full set) by default with links to the section in the
> documentation where the reasoning for said omission is explained and/or
> affirmed.

I was just wondering whether that wouldn't be a shorter list.
It's not hard to get the base types that don't have btree opclasses:

select typname from pg_type where not exists
(select 1 from pg_opclass where opcmethod = 403 and opcdefault and opcintype = pg_type.oid) and typtype = 'b' and not (typelem!=0 and typlen=-1) order by 1;
typname
---------------
aclitem
box
cid
cidr
circle
gtsvector
json
line
lseg
path
pg_node_tree
point
polygon
refcursor
regclass
regconfig
regdictionary
regoper
regoperator
regproc
regprocedure
regtype
smgr
txid_snapshot
unknown
varchar
xid
xml
(28 rows)

although this is misleading because some of these are binary-coercible to
indexable types, which means that the indexable type's opclass works for
them.

Eliminating those, we get

select typname from pg_type where not exists
(select 1 from pg_opclass where opcmethod = 403 and opcdefault and binary_coercible(pg_type.oid, opcintype)) and typtype = 'b' and not (typelem!=0 and typlen=-1) order by 1;
typname
---------------
aclitem haven't bothered, no obvious sort order anyway
box no linear sort order
cid haven't bothered
circle no linear sort order
gtsvector internal type, wouldn't be useful
json
line no linear sort order
lseg no linear sort order
path no linear sort order
point no linear sort order
polygon no linear sort order
refcursor haven't bothered
smgr useless legacy type
txid_snapshot no linear sort order
unknown there are no operations for 'unknown'
xid no linear sort order (yes, really)
xml
(17 rows)

So really we're pretty close to being able to say "there are comparison
operators for every built-in type for which it's sensible".

regards, tom lane