Thread: 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 <peter_e@gmx.net>
Date: Thu May 15 15:50:21 2003 +0000
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
Attachment
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:
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.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Feb 8, 2018 at 6:26 AM, Adrien Nayrat <adrien.nayrat@dalibo.com>
> 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