Re: [HACKERS] comparison operators
* Andrew Dunstan (and...@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 signature.asc Description: Digital signature
Re: [HACKERS] comparison operators
Stephen Frost sfr...@snowman.net writes: * Andrew Dunstan (and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] comparison operators
* Tom Lane (t...@sss.pgh.pa.us) wrote: Stephen Frost sfr...@snowman.net writes: * Andrew Dunstan (and...@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 signature.asc Description: Digital signature
Re: [HACKERS] comparison operators
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 lt; andrew@ gt; 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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] comparison operators
David G Johnston david.g.johns...@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!=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 --- aclitemhaven't bothered, no obvious sort order anyway boxno linear sort order cidhaven'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 polygonno linear sort order refcursor haven't bothered smgr useless legacy type txid_snapshot no linear sort order unknownthere are no operations for 'unknown' xidno 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] comparison operators
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] comparison operators
Andrew Dunstan and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] comparison operators
On 2014-06-17 19:22:07 -0400, Tom Lane wrote: Andrew Dunstan and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] comparison operators
On 06/17/2014 07:25 PM, Andres Freund wrote: On 2014-06-17 19:22:07 -0400, Tom Lane wrote: Andrew Dunstan and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Comparison operators for bytea
I notice that in 7.0.3 there are no '' or '' operators defined for the bytea type. Is this (should this be) fixed in 7.l? It just makes it impossible to do stuff like SELECT DISTINCT (byteafield), etc... The reason i'm trying to do this is I have written a query that discovers all the foreign keys in a table, (meaning I could actually modify pg_dump to use ALTER TABLE ADD FOREIGN KEY statements instead of ADD CONSTRAINT TRIGGER), however I need to do a DISTINCT across pg_trigger.tgargs... Chris -- Christopher Kings-Lynne Family Health Network (ACN 089 639 243)
