Re: comparison operators

David G Johnston <david.g.johnston@gmail.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!=0and typlen=-1) order by 1;   typname    
 

---------------aclitemboxcidcidrcirclegtsvectorjsonlinelsegpathpg_node_treepointpolygonrefcursorregclassregconfigregdictionaryregoperregoperatorregprocregprocedureregtypesmgrtxid_snapshotunknownvarcharxidxml
(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 anywaybox            no linear sort ordercid
     haven't botheredcircle            no linear sort ordergtsvector        internal type, wouldn't be usefuljsonline
        no linear sort orderlseg            no linear sort orderpath            no linear sort orderpoint            no
linearsort orderpolygon        no linear sort orderrefcursor        haven't botheredsmgr            useless legacy
typetxid_snapshot       no linear sort orderunknown        there are no operations for 'unknown'xid            no
linearsort 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