Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1ejoC5-0007vW-LX for pgsql-docs@arkaria.postgresql.org; Thu, 08 Feb 2018 15:33:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ejoC4-0008RO-GB for pgsql-docs@arkaria.postgresql.org; Thu, 08 Feb 2018 15:33:00 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ejoC4-0008RE-2s for pgsql-docs@lists.postgresql.org; Thu, 08 Feb 2018 15:33:00 +0000 Received: from mail-qt0-x244.google.com ([2607:f8b0:400d:c0d::244]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ejoBw-0001sQ-56 for pgsql-docs@postgresql.org; Thu, 08 Feb 2018 15:32:58 +0000 Received: by mail-qt0-x244.google.com with SMTP id s27so6650842qts.4 for ; Thu, 08 Feb 2018 07:32:51 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=m3dYiMGOMgels9OR7nyFbxJRUXw1PDVbyZjkX9ezfr0=; b=nTsFqMqg5/aCx5YM8f2Sn20FXdLC7MyM/st7jyfif0EA7nsyGnSzfkOsLJB41tGd+K dmkf9UT4JdtHHvdi/v6ceDPMG0+tq6lvvEwvr+Cn46h5gqk9OsG1pCfaMVzQYmqDEkC5 XJPCefr1YiyadeSvjdfpoRSONLji8cYWL7Lndlvs5eJvQT17pAMmbZTbZU8ZYvO1lKXL rMguCMmhaHQqhATaLPgP7y15bmhL28iPobillefVCrzxMBKeFZspoPNZ6UKPWJnsHuFv GCi1e9oLcJaklGpslJ3jr+waWMh71hcWySS0VZ+G8f7ugRPOxXGXYWjl7svRTJYKUNNh j1SA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=m3dYiMGOMgels9OR7nyFbxJRUXw1PDVbyZjkX9ezfr0=; b=SD6pwNCTC0OX6x5juQib7nnx+skD2OejHh4RAm/wXJG5DleFCwpJhLUyXJ32hUtRMw 1OQXaZNL9kuzTVAg99UkIfsWLqQgzKz/C8FeQVTd1ktyLzMGhcxAZKA9SkPluXJ8LPwd tmkZY6rq210QOAJia619TRkkUZbwZFwtqTXBareABCBTida6qvKnL2jyNcEXTJzp7zD7 1z3yEyCzbJ10GqIgRYQCUGwpF8kkwjCs5ThmF4KsDZEeWh7Yfej3oNUL7xWuUqP9HnIw fBVaaAyALdeY4Mh4dTRrHGKLA8/f0wEESAHDkFpAxanLxk4Rlo82nuli8M7uRJgytj0W i4Aw== X-Gm-Message-State: APf1xPDGmIlmNnP5Ufg+7bo1crTcMl21cEnEFP2aoXYHJnbGTUbtw5Rl puTNEX69EiugwfiyUWRZGoOg6CCJ5DWlq+s2sSSiBJtO X-Google-Smtp-Source: AH8x227Z3Pv97sCE7yJptdT2fEzIUK2eFoWpXf5quGw3KNzwJyMYpHRZ+UIEBb1yGp+WVz6f+9mTXojEzz/uH/iOypI= X-Received: by 10.237.46.132 with SMTP id k4mr1644698qtd.325.1518103971134; Thu, 08 Feb 2018 07:32:51 -0800 (PST) MIME-Version: 1.0 Received: by 10.12.135.38 with HTTP; Thu, 8 Feb 2018 07:32:50 -0800 (PST) In-Reply-To: <92d30118-9933-3674-a95b-f84c97abde6c@dalibo.com> References: <92d30118-9933-3674-a95b-f84c97abde6c@dalibo.com> From: "David G. Johnston" Date: Thu, 8 Feb 2018 08:32:50 -0700 Message-ID: Subject: Re: Text operators "~<=~ ~<~ ~>=~ ~>~" not documented To: Adrien Nayrat Cc: pgsql-docs@postgresql.org Content-Type: multipart/alternative; boundary="94eb2c0678c6ee81cf0564b51f55" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --94eb2c0678c6ee81cf0564b51f55 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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. > =E2=80=8B[..]=E2=80=8B > > > ~<~ 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 > 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=3D0.43..2.46 rows= =3D54 > 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-opclasses.html > > =E2=80=8B 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. --94eb2c0678c6ee81cf0564b51f55 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Fe= b 8, 2018 at 6:26 AM, Adrien Nayrat <adrien.nayrat@dalibo.com> wrote:
Hello,

While reading [1] I notice $subject operators lacks of explanation in
documentation.
=E2=80=8B[..]=E2=80=8B


~<~=C2=A0 =C2=A0 =C2=A0less than
~>~=C2=A0 =C2=A0 =C2=A0greater than
~<=3D~=C2=A0 =C2=A0 less than or equal to
~>=3D~=C2=A0 =C2=A0 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=C2=A0 ( location text_pattern_ops);
explain select * from users where location like 'test%';
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN
-----------------------------------------------------------------= -------------------
=C2=A0Index Scan using users_location_idx1 on users=C2=A0 (cost=3D0.43..2.4= 6 rows=3D54 width=3D201)
=C2=A0 =C2=A0Index Cond: ((location ~>=3D~ 'test'::text) AND (lo= cation ~<~ 'tesu'::text))
=C2=A0 =C2=A0Filter: (location ~~ 'test%'::text)
(3 lignes)


Should we add them on this page?
https://www.postgresql.org/do= cs/devel/static/functions-string.html

Thanks!


1: https://www.postgresq= l.org/docs/current/static/spgist-builtin-opclasses.html

=E2=80=8B
I'd be inclined to remove those four operators from the= spgist page's table and replace them with "LIKE".=C2=A0 Then= in the text below the table explain that LIKE is implemented using a combi= nation of those four operators.

If we do add them to the funct= ions and operators chapter it should be in the pattern matching section:

https://www.postgresql.o= rg/docs/devel/static/functions-matching.html


9.7.1 LIKE needs a table containing [!]~~ and [!]~~* added any= way; tacking these on as well and noting them as implementation functions (= i.e., not something a user would want to invoke directly typically) would b= e appropriate.

David J.
--94eb2c0678c6ee81cf0564b51f55--