public inbox for [email protected]
help / color / mirror / Atom feedText operators "~<=~ ~<~ ~>=~ ~>~" not documented
3+ messages / 3 participants
[nested] [flat]
* Text operators "~<=~ ~<~ ~>=~ ~>~" not documented
@ 2018-02-08 13:26 Adrien Nayrat <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Adrien Nayrat @ 2018-02-08 13:26 UTC (permalink / raw)
To: pgsql-docs
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 protected]>
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
Attachments:
[application/pgp-signature] signature.asc (488B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Text operators "~<=~ ~<~ ~>=~ ~>~" not documented
@ 2018-02-08 15:32 David G. Johnston <[email protected]>
parent: Adrien Nayrat <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2018-02-08 15:32 UTC (permalink / raw)
To: Adrien Nayrat <[email protected]>; +Cc: pgsql-docs
On Thu, Feb 8, 2018 at 6:26 AM, Adrien Nayrat <[email protected]>
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Text operators "~<=~ ~<~ ~>=~ ~>~" not documented
@ 2018-02-08 15:42 Tom Lane <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2018-02-08 15:42 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Adrien Nayrat <[email protected]>; pgsql-docs
"David G. Johnston" <[email protected]> writes:
> On Thu, Feb 8, 2018 at 6:26 AM, Adrien Nayrat <[email protected]>
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2018-02-08 15:42 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-02-08 13:26 Text operators "~<=~ ~<~ ~>=~ ~>~" not documented Adrien Nayrat <[email protected]>
2018-02-08 15:32 ` David G. Johnston <[email protected]>
2018-02-08 15:42 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox