Text operators "~<=~ ~<~ ~>=~ ~>~" not documented
Hello, While reading [1] I notice $subject operators lacks of explanation in documentation. They where introduced in this commit : commit 2c0556068fc308ed9cce06c85de7e42305d34b86 Author: Peter Eisentraut Date: Thu May 15 15:50:21 2003 + Indexing support for pattern matching operations via separate operator class when lc_collate is not C Comment from this commit : /* * The following operators support character-by-character comparison * of text data types, to allow building indexes suitable for LIKE * clauses. */ So if I understand correctly : ~<~ less than ~>~ greater than ~<=~less than or equal to ~>=~greater than or equal to For all, it is a character-by-character comparison regardless of collation. We encounter them when we use text_pattern_ops for example : create index ON users ( location text_pattern_ops); explain select * from users where location like 'test%'; QUERY PLAN Index Scan using users_location_idx1 on users (cost=0.43..2.46 rows=54 width=201) Index Cond: ((location ~>=~ 'test'::text) AND (location ~<~ 'tesu'::text)) Filter: (location ~~ 'test%'::text) (3 lignes) Should we add them on this page? https://www.postgresql.org/docs/devel/static/functions-string.html Thanks! 1: https://www.postgresql.org/docs/current/static/spgist-builtin-opclasses.html -- Adrien NAYRAT http://dalibo.com - http://dalibo.org signature.asc Description: OpenPGP digital signature
Re: Text operators "~<=~ ~<~ ~>=~ ~>~" not documented
On Thu, Feb 8, 2018 at 6:26 AM, Adrien Nayrat wrote: > Hello, > > While reading [1] I notice $subject operators lacks of explanation in > documentation. > [..] > > > ~<~ less than > ~>~ greater than > ~<=~less than or equal to > ~>=~greater than or equal to > > For all, it is a character-by-character comparison regardless of > collation. We > encounter them when we use text_pattern_ops for example : > > create index ON users ( location text_pattern_ops); > explain select * from users where location like 'test%'; > QUERY PLAN > > > Index Scan using users_location_idx1 on users (cost=0.43..2.46 rows=54 > width=201) >Index Cond: ((location ~>=~ 'test'::text) AND (location ~<~ > 'tesu'::text)) >Filter: (location ~~ 'test%'::text) > (3 lignes) > > > Should we add them on this page? > https://www.postgresql.org/docs/devel/static/functions-string.html > > Thanks! > > > 1: https://www.postgresql.org/docs/current/static/spgist- > builtin-opclasses.html > > I'd be inclined to remove those four operators from the spgist page's table and replace them with "LIKE". Then in the text below the table explain that LIKE is implemented using a combination of those four operators. If we do add them to the functions and operators chapter it should be in the pattern matching section: https://www.postgresql.org/docs/devel/static/functions-matching.html 9.7.1 LIKE needs a table containing [!]~~ and [!]~~* added anyway; tacking these on as well and noting them as implementation functions (i.e., not something a user would want to invoke directly typically) would be appropriate. David J.
Re: Text operators "~<=~ ~<~ ~>=~ ~>~" not documented
"David G. Johnston" writes: > On Thu, Feb 8, 2018 at 6:26 AM, Adrien Nayrat > wrote: >> While reading [1] I notice $subject operators lacks of explanation in >> documentation. > I'd be inclined to remove those four operators from the spgist page's table > and replace them with "LIKE". Then in the text below the table explain > that LIKE is implemented using a combination of those four operators. I believe it's intentional that those operators aren't documented: we don't want people to get in the habit of using them directly. (These days, if you actually need what they do, the approved way to spell it is like 'x < y COLLATE "C"'.) So actually, my inclination would be to remove them from the spgist table and put nothing back. Implying that spgist text_ops fully supports LIKE would certainly be wrong/confusing --- we do not claim in the main part of the docs that btree text_ops supports LIKE, even though it has a comparable level of support as long as you're using C collation. Since these operators are user-visible in EXPLAIN output, there might be merit in mentioning them in passing in the LIKE docs. But we should not put them in a table with the LIKE ops themselves, IMO. That would just invite confusion about what they do and whether you're supposed to use them directly. regards, tom lane
