Thread: comparison operators

comparison operators

From
Andrew Dunstan
Date:
I went to have a look at documenting the jsonb comparison operators, and 
found that the docs on comparison operators contain this:
   Comparison operators are available for all relevant data types.

They neglect to specify further, however. This doesn't seem very 
satisfactory. How is a user to know which are relevant? I know they are 
not available for xml and json, but are for jsonb. Just talking about 
"all relevant types" seems rather hand-wavy.

Thoughts?

cheers

andrew




Re: comparison operators

From
Tom Lane
Date:
Andrew Dunstan <andrew@dunslane.net> writes:
> I went to have a look at documenting the jsonb comparison operators, and 
> found that the docs on comparison operators contain this:

>     Comparison operators are available for all relevant data types.

> They neglect to specify further, however. This doesn't seem very 
> satisfactory. How is a user to know which are relevant? I know they are 
> not available for xml and json, but are for jsonb. Just talking about 
> "all relevant types" seems rather hand-wavy.

Well, there are 38 default btree opclasses in the standard system ATM.
Are we worried enough about this to list them all explicitly?  Given the
lack of complaints to date, I'm not.

However, if we try to fudge it by saying something like "available for
all data types for which there is a natural linear order", I'm not
sure that that's 100% true; and it's certainly not complete, since
for instance jsonb's ordering is rather artificial, and the area-based
orderings of the built-in geometric types are even more so.
        regards, tom lane



Re: comparison operators

From
Andres Freund
Date:
On 2014-06-17 19:22:07 -0400, Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
> > I went to have a look at documenting the jsonb comparison operators, and 
> > found that the docs on comparison operators contain this:
> 
> >     Comparison operators are available for all relevant data types.
> 
> > They neglect to specify further, however. This doesn't seem very 
> > satisfactory. How is a user to know which are relevant? I know they are 
> > not available for xml and json, but are for jsonb. Just talking about 
> > "all relevant types" seems rather hand-wavy.
> 
> Well, there are 38 default btree opclasses in the standard system ATM.
> Are we worried enough about this to list them all explicitly?  Given the
> lack of complaints to date, I'm not.
> 
> However, if we try to fudge it by saying something like "available for
> all data types for which there is a natural linear order", I'm not
> sure that that's 100% true; and it's certainly not complete, since
> for instance jsonb's ordering is rather artificial, and the area-based
> orderings of the built-in geometric types are even more so.

It's not true for e.g. xid (which is rather annoying btw).

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services



Re: comparison operators

From
Andrew Dunstan
Date:
On 06/17/2014 07:25 PM, Andres Freund wrote:
> On 2014-06-17 19:22:07 -0400, Tom Lane wrote:
>> Andrew Dunstan <andrew@dunslane.net> writes:
>>> I went to have a look at documenting the jsonb comparison operators, and
>>> found that the docs on comparison operators contain this:
>>>      Comparison operators are available for all relevant data types.
>>> They neglect to specify further, however. This doesn't seem very
>>> satisfactory. How is a user to know which are relevant? I know they are
>>> not available for xml and json, but are for jsonb. Just talking about
>>> "all relevant types" seems rather hand-wavy.
>> Well, there are 38 default btree opclasses in the standard system ATM.
>> Are we worried enough about this to list them all explicitly?  Given the
>> lack of complaints to date, I'm not.
>>
>> However, if we try to fudge it by saying something like "available for
>> all data types for which there is a natural linear order", I'm not
>> sure that that's 100% true; and it's certainly not complete, since
>> for instance jsonb's ordering is rather artificial, and the area-based
>> orderings of the built-in geometric types are even more so.
> It's not true for e.g. xid (which is rather annoying btw).
>


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.

For json/jsonb I think I'll just add a para saying we have them for 
jsonb and not for json.

cheers

andrew



Re: comparison operators

From
Stephen Frost
Date:
* Andrew Dunstan (andrew@dunslane.net) 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.

Perhaps with a link to where the informaiton about which exist is
available..?  Or a query to get the list?

In general, I agree with you.
Thanks,
    Stephen

Re: comparison operators

From
Tom Lane
Date:
Stephen Frost <sfrost@snowman.net> writes:
> * Andrew Dunstan (andrew@dunslane.net) 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.

> Perhaps with a link to where the informaiton about which exist is
> available..?  Or a query to get the list?

Queries for this sort of thing are covered in the chapter about index
opclasses.  The basic query would be like

select opcintype::regtype from pg_opclass where opcmethod = 403 and opcdefault;

but I'm not sure if this is completely useful; it's not obvious for
example that the "text" opclass is also used for varchar.  Another
point is that some of the operators aren't named in the conventional
way.
        regards, tom lane



Re: comparison operators

From
Stephen Frost
Date:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > * Andrew Dunstan (andrew@dunslane.net) 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.
>
> > Perhaps with a link to where the informaiton about which exist is
> > available..?  Or a query to get the list?
>
> Queries for this sort of thing are covered in the chapter about index
> opclasses.  The basic query would be like

Right, a link to there from this would be useful, imv.

> select opcintype::regtype from pg_opclass where opcmethod = 403 and opcdefault;
>
> but I'm not sure if this is completely useful; it's not obvious for
> example that the "text" opclass is also used for varchar.  Another
> point is that some of the operators aren't named in the conventional
> way.

Good point.  Hopefully a link over to the index-opclasses.html would be
helpful to users exploring these questions.
Thanks,
    Stephen

Re: comparison operators

From
David G Johnston
Date:
Andrew Dunstan wrote
> On 06/17/2014 07:25 PM, Andres Freund wrote:
>> On 2014-06-17 19:22:07 -0400, Tom Lane wrote:
>>> Andrew Dunstan <

> andrew@

> > writes:
>>>> I went to have a look at documenting the jsonb comparison operators,
>>>> and
>>>> found that the docs on comparison operators contain this:
>>>>      Comparison operators are available for all relevant data types.
>>>> They neglect to specify further, however. This doesn't seem very
>>>> satisfactory. How is a user to know which are relevant? I know they are
>>>> not available for xml and json, but are for jsonb. Just talking about
>>>> "all relevant types" seems rather hand-wavy.
>>> Well, there are 38 default btree opclasses in the standard system ATM.
>>> Are we worried enough about this to list them all explicitly?  Given the
>>> lack of complaints to date, I'm not.
> 
> 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.

My other reaction is that referring to data types at all in this section is
unnecessary - other than maybe to state (which it does not currently) that
both sides of the comparison must be of the same (or binary equivalent, like
text/varchar) type or there must exist an implicit cast for one of the
operands.  Much of that knowledge is implied and well understood though, as
is the fact that operators are closely associated with data types.  IOW - I
would be fine with removing "Comparison operators are available for all
relevant data types" and not replacing it with anything.  Though "for many
data types" is my preferred equivalent phrase for the same reasons Andrew
noted.

David J.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/comparison-operators-tp5807654p5807757.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.



Re: comparison operators

From
Tom Lane
Date:
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