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
