Subject: Text operators "~<=~ ~<~ ~>=~ ~>~" not documented

From: Adrien Nayrat <[email-masked]>
Date: Thu, 8 Feb 2018 14:26:49 +0100

Hello,

While reading [1] I notice $subject operators lacks of explanation in
documentation.

They where introduced in this commit :

commit 2c0556068fc308ed9cce06c85de7e42305d34b86
Author: Peter Eisentraut <[email-masked]>
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

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 8 Feb 2018 08:32:50 -0700

On Thu, Feb 8, 2018 at 6:26 AM, Adrien Nayrat <[email-masked]>
wrote:

> ​[..]​
> ​
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.

---

From: Tom Lane <[email-masked]>
Date: Thu, 08 Feb 2018 10:42:34 -0500

> 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