From: Adrien Nayrat <adrien.nayrat@dalibo.com>
Subject: Text operators "~<=~ ~<~ ~>=~ ~>~" not documented
To: pgsql-docs@postgreSQL.org
Message-ID: <92d30118-9933-3674-a95b-f84c97abde6c@dalibo.com>
Date: Thu, 8 Feb 2018 14:26:49 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.6.0
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="wagiRdHMhCk4jMSHwRtLBUDfKZImzlkTn"
Precedence: bulk

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--wagiRdHMhCk4jMSHwRtLBUDfKZImzlkTn
Content-Type: multipart/mixed; boundary="RgE0fOaQYZjZO0rb7XpaZwsrjTUvvVCpd";
 protected-headers="v1"
From: Adrien Nayrat <adrien.nayrat@dalibo.com>
To: pgsql-docs@postgreSQL.org
Message-ID: <92d30118-9933-3674-a95b-f84c97abde6c@dalibo.com>
Subject: Text operators "~<=~ ~<~ ~>=~ ~>~" not documented

--RgE0fOaQYZjZO0rb7XpaZwsrjTUvvVCpd
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: quoted-printable

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 operato=
r
    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
~<=3D~ 	less than or equal to
~>=3D~ 	greater than or equal to

For all, it is a character-by-character comparison regardless of collatio=
n. 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=3D0.43..2.46 rows=3D=
54 width=3D201)
   Index Cond: ((location ~>=3D~ '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-opclasse=
s.html


--=20
Adrien NAYRAT

http://dalibo.com - http://dalibo.org


--RgE0fOaQYZjZO0rb7XpaZwsrjTUvvVCpd--

--wagiRdHMhCk4jMSHwRtLBUDfKZImzlkTn
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQEzBAEBCAAdFiEEm0vCLGPoatLA5LoUsupItzJ4s38FAlp8UBkACgkQsupItzJ4
s3+WyAf/UCwRg1pxKZdkji1l4vQORTm7xxPqFrcbFZz5ajdjjVQSo6DcIEhFL5+M
WSNFBOjbo/NmC1q+wbKIPgg8sgnq9GSdZjSETX+5my5uDkCzQpyLeBiNoLpEDUur
sTi616AdXQZX8FmyspWNPe+iPA7bWXJuybPSLqC/GsvmAu0CIrrgteIcrfB4vyoO
JheaeF8z7qSZAuM08FIwne28HN0haAgdS/qKLOzptPwtRwVVm2YaQe1zSkeqk8ee
aRTVMx2euup8wQUBg/0U2zMCTkiTk0Yggf0eZpJcOLgTM3Md4cXTmR5eAJdZ03/V
fqJOLqYekmOx6il1LgxyAM0ivuXkhg==
=vcfq
-----END PGP SIGNATURE-----

--wagiRdHMhCk4jMSHwRtLBUDfKZImzlkTn--