pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedLower or Upper case for F.33. pg_trgm
10+ messages / 5 participants
[nested] [flat]
* Lower or Upper case for F.33. pg_trgm
@ 2022-08-16 10:17 PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 10+ messages in thread
From: PG Doc comments form @ 2022-08-16 10:17 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: marcmaiwald@googlemail.com
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/14/pgtrgm.html
Description:
Hey guys,
I have a question regarding the trigram algorithm and I can not find any
information about it in your documentation:
Do you distinguish between lower and uppercase? Or do you consider all words
in lowercase?
Happy to get a short feedback from you,
Greetings, Marc
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Lower or Upper case for F.33. pg_trgm
@ 2022-08-16 10:36 Daniel Gustafsson <daniel@yesql.se>
parent: PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 10+ messages in thread
From: Daniel Gustafsson @ 2022-08-16 10:36 UTC (permalink / raw)
To: marcmaiwald@googlemail.com; pgsql-docs@lists.postgresql.org
> On 16 Aug 2022, at 12:17, PG Doc comments form <noreply@postgresql.org> wrote:
> I have a question regarding the trigram algorithm and I can not find any
> information about it in your documentation:
Maybe we should add something about this?
> Do you distinguish between lower and uppercase? Or do you consider all words
> in lowercase?
There is support for compiling pg_trgm case sensitive, but it's by default case
insensitive.
# SELECT word_similarity('word', 'WORD');
word_similarity
-----------------
1
(1 row)
> Happy to get a short feedback from you,
I would recommend the pg_general mailinglist as that will be a safer way to get
general questions answered.
--
Daniel Gustafsson https://vmware.com/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Lower or Upper case for F.33. pg_trgm
@ 2022-08-16 10:54 Erik Rijkers <er@xs4all.nl>
parent: Daniel Gustafsson <daniel@yesql.se>
0 siblings, 1 reply; 10+ messages in thread
From: Erik Rijkers @ 2022-08-16 10:54 UTC (permalink / raw)
To: Daniel Gustafsson <daniel@yesql.se>; marcmaiwald@googlemail.com; pgsql-docs@lists.postgresql.org
Op 16-08-2022 om 12:36 schreef Daniel Gustafsson:
>> On 16 Aug 2022, at 12:17, PG Doc comments form <noreply@postgresql.org> wrote:
>
>> I have a question regarding the trigram algorithm and I can not find any
>> information about it in your documentation:
>
> Maybe we should add something about this?
Yeah, it's a bit strange that none of the following strings yield any
info on that page: 'case', 'sensitiv', 'upper', 'lower', and that there
is no mention of the ~ versus ~* difference.
Maybe worth to (already in pgtrgm.html) give the simple hint:
~ is case-sensitive
~* is case-insensitive
In any case a link to functions-matching.html seems indicated.
Erik Rijkers
>
>> Do you distinguish between lower and uppercase? Or do you consider all words
>> in lowercase?
>
> There is support for compiling pg_trgm case sensitive, but it's by default case
> insensitive.
>
> # SELECT word_similarity('word', 'WORD');
> word_similarity
> -----------------
> 1
> (1 row)
>
>> Happy to get a short feedback from you,
>
> I would recommend the pg_general mailinglist as that will be a safer way to get
> general questions answered.
>
> --
> Daniel Gustafsson https://vmware.com/
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Lower or Upper case for F.33. pg_trgm
@ 2022-08-16 11:46 Daniel Gustafsson <daniel@yesql.se>
parent: Erik Rijkers <er@xs4all.nl>
0 siblings, 2 replies; 10+ messages in thread
From: Daniel Gustafsson @ 2022-08-16 11:46 UTC (permalink / raw)
To: Erik Rijkers <er@xs4all.nl>; +Cc: marcmaiwald@googlemail.com; pgsql-docs@lists.postgresql.org
> On 16 Aug 2022, at 12:54, Erik Rijkers <er@xs4all.nl> wrote:
>
> Op 16-08-2022 om 12:36 schreef Daniel Gustafsson:
>>> On 16 Aug 2022, at 12:17, PG Doc comments form <noreply@postgresql.org> wrote:
>>> I have a question regarding the trigram algorithm and I can not find any
>>> information about it in your documentation:
>> Maybe we should add something about this?
>
> Yeah, it's a bit strange that none of the following strings yield any info on that page: 'case', 'sensitiv', 'upper', 'lower', and that there is no mention of the ~ versus ~* difference.
>
> Maybe worth to (already in pgtrgm.html) give the simple hint:
> ~ is case-sensitive
> ~* is case-insensitive
>
> In any case a link to functions-matching.html seems indicated.
Yeah, I think there is room for improvements here. Are you up for drafting a
patch for this?
--
Daniel Gustafsson https://vmware.com/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Lower or Upper case for F.33. pg_trgm
@ 2022-08-16 12:35 Marc M. <marcmaiwald@googlemail.com>
parent: Daniel Gustafsson <daniel@yesql.se>
1 sibling, 0 replies; 10+ messages in thread
From: Marc M. @ 2022-08-16 12:35 UTC (permalink / raw)
To: daniel@yesql.se; +Cc: Erik Rijkers <er@xs4all.nl>; pgsql-docs@lists.postgresql.org
Thanks for your fast response.
Is this a question for me? I am fine with a short hint regarding the
default.
A link to another documentation is also fine.
Am Di., 16. Aug. 2022 um 13:46 Uhr schrieb Daniel Gustafsson <
daniel@yesql.se>:
> > On 16 Aug 2022, at 12:54, Erik Rijkers <er@xs4all.nl> wrote:
> >
> > Op 16-08-2022 om 12:36 schreef Daniel Gustafsson:
> >>> On 16 Aug 2022, at 12:17, PG Doc comments form <noreply@postgresql.org>
> wrote:
> >>> I have a question regarding the trigram algorithm and I can not find
> any
> >>> information about it in your documentation:
> >> Maybe we should add something about this?
> >
> > Yeah, it's a bit strange that none of the following strings yield any
> info on that page: 'case', 'sensitiv', 'upper', 'lower', and that there is
> no mention of the ~ versus ~* difference.
> >
> > Maybe worth to (already in pgtrgm.html) give the simple hint:
> > ~ is case-sensitive
> > ~* is case-insensitive
> >
> > In any case a link to functions-matching.html seems indicated.
>
> Yeah, I think there is room for improvements here. Are you up for
> drafting a
> patch for this?
>
> --
> Daniel Gustafsson https://vmware.com/
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Lower or Upper case for F.33. pg_trgm
@ 2022-08-16 13:03 Erik Rijkers <er@xs4all.nl>
parent: Daniel Gustafsson <daniel@yesql.se>
1 sibling, 1 reply; 10+ messages in thread
From: Erik Rijkers @ 2022-08-16 13:03 UTC (permalink / raw)
To: Daniel Gustafsson <daniel@yesql.se>; +Cc: marcmaiwald@googlemail.com; pgsql-docs@lists.postgresql.org
Op 16-08-2022 om 13:46 schreef Daniel Gustafsson:
>> On 16 Aug 2022, at 12:54, Erik Rijkers <er@xs4all.nl> wrote:
>>
>> Op 16-08-2022 om 12:36 schreef Daniel Gustafsson:
>>>> On 16 Aug 2022, at 12:17, PG Doc comments form <noreply@postgresql.org> wrote:
>>>> I have a question regarding the trigram algorithm and I can not find any
>>>> information about it in your documentation:
>>> Maybe we should add something about this?
>>
>> Yeah, it's a bit strange that none of the following strings yield any info on that page: 'case', 'sensitiv', 'upper', 'lower', and that there is no mention of the ~ versus ~* difference.
>>
>> Maybe worth to (already in pgtrgm.html) give the simple hint:
>> ~ is case-sensitive
>> ~* is case-insensitive
>>
>> In any case a link to functions-matching.html seems indicated.
>
> Yeah, I think there is room for improvements here. Are you up for drafting a
> patch for this?
>
How is this?
(bluntly stating 'similarity comparisons are case-insensitive' -
although I'm not really sure..)
Erik
> --
> Daniel Gustafsson https://vmware.com/
>
Attachments:
[text/x-patch] pgtrgm.sgml.20220816.diff (1.1K, ../../4be605ae-7fbb-c47c-9c7f-f8965ff0ea9e@xs4all.nl/2-pgtrgm.sgml.20220816.diff)
download | inline diff:
--- ./doc/src/sgml/pgtrgm.sgml.orig 2022-08-16 14:50:08.586555358 +0200
+++ ./doc/src/sgml/pgtrgm.sgml 2022-08-16 14:56:39.358617804 +0200
@@ -416,6 +416,8 @@
the above-described similarity operators, and additionally support
trigram-based index searches for <literal>LIKE</literal>, <literal>ILIKE</literal>,
<literal>~</literal>, <literal>~*</literal> and <literal>=</literal> queries.
+ The similarity comparisons are case-insensitive, but these queries can be
+ case-sensitive (see <xref linkend="functions-matching"/>).
Inequality operators are not supported.
Note that those indexes may not be as efficient as regular B-tree indexes
for equality operator.
@@ -534,7 +536,8 @@
<para>
Beginning in <productname>PostgreSQL</productname> 9.3, these index types also support
index searches for regular-expression matches
- (<literal>~</literal> and <literal>~*</literal> operators), for example
+ (<literal>~</literal> and <literal>~*</literal> operators, resp. case-sensitive and
+ case-insensitive), for example
<programlisting>
SELECT * FROM test_trgm WHERE t ~ '(foo|bar)';
</programlisting>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Lower or Upper case for F.33. pg_trgm
@ 2022-08-16 13:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Erik Rijkers <er@xs4all.nl>
0 siblings, 2 replies; 10+ messages in thread
From: Tom Lane @ 2022-08-16 13:53 UTC (permalink / raw)
To: Erik Rijkers <er@xs4all.nl>; +Cc: Daniel Gustafsson <daniel@yesql.se>; marcmaiwald@googlemail.com; pgsql-docs@lists.postgresql.org
Erik Rijkers <er@xs4all.nl> writes:
> (bluntly stating 'similarity comparisons are case-insensitive' -
> although I'm not really sure..)
Perhaps like "similarity comparisons are case-insensitive in a
standard build of pg_trgm", if you want to nod to the existence
of a compile option without going into detail.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Lower or Upper case for F.33. pg_trgm
@ 2022-08-16 14:16 Marc M. <marcmaiwald@googlemail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 10+ messages in thread
From: Marc M. @ 2022-08-16 14:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Erik Rijkers <er@xs4all.nl>; Daniel Gustafsson <daniel@yesql.se>; pgsql-docs@lists.postgresql.org
Sounds good to me.
Am Di., 16. Aug. 2022 um 15:53 Uhr schrieb Tom Lane <tgl@sss.pgh.pa.us>:
> Erik Rijkers <er@xs4all.nl> writes:
> > (bluntly stating 'similarity comparisons are case-insensitive' -
> > although I'm not really sure..)
>
> Perhaps like "similarity comparisons are case-insensitive in a
> standard build of pg_trgm", if you want to nod to the existence
> of a compile option without going into detail.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Lower or Upper case for F.33. pg_trgm
@ 2022-08-16 21:28 Daniel Gustafsson <daniel@yesql.se>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 10+ messages in thread
From: Daniel Gustafsson @ 2022-08-16 21:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Erik Rijkers <er@xs4all.nl>; marcmaiwald@googlemail.com; pgsql-docs@lists.postgresql.org
> On 16 Aug 2022, at 15:53, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Erik Rijkers <er@xs4all.nl> writes:
>> (bluntly stating 'similarity comparisons are case-insensitive' -
>> although I'm not really sure..)
>
> Perhaps like "similarity comparisons are case-insensitive in a
> standard build of pg_trgm", if you want to nod to the existence
> of a compile option without going into detail.
Looking at this I'm leaning towards paring down the diff posted upthread with
pretty much this, I think that will provide value while avoid causing
confusion.
As a related side note, there are four instances of "case insensitive{ly}" in
the docs with all other instances using "case-insensitive{ly}". I'm inclined
to fix those four to use a dash while at it to be consistent across all pages.
--
Daniel Gustafsson https://vmware.com/
Attachments:
[application/octet-stream] pg_trgm_case.diff (680B, ../../7657EDEE-5EE2-4AAB-BA95-47B4F71653E1@yesql.se/2-pg_trgm_case.diff)
download | inline diff:
diff --git a/doc/src/sgml/pgtrgm.sgml b/doc/src/sgml/pgtrgm.sgml
index 7e29282255..fefe30afb7 100644
--- a/doc/src/sgml/pgtrgm.sgml
+++ b/doc/src/sgml/pgtrgm.sgml
@@ -416,6 +416,8 @@
the above-described similarity operators, and additionally support
trigram-based index searches for <literal>LIKE</literal>, <literal>ILIKE</literal>,
<literal>~</literal>, <literal>~*</literal> and <literal>=</literal> queries.
+ The similarity comparisons are case-insensitive in a default build of
+ <filename>pg_trgm</filename>.
Inequality operators are not supported.
Note that those indexes may not be as efficient as regular B-tree indexes
for equality operator.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Lower or Upper case for F.33. pg_trgm
@ 2022-08-16 21:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Daniel Gustafsson <daniel@yesql.se>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2022-08-16 21:49 UTC (permalink / raw)
To: Daniel Gustafsson <daniel@yesql.se>; +Cc: Erik Rijkers <er@xs4all.nl>; marcmaiwald@googlemail.com; pgsql-docs@lists.postgresql.org
Daniel Gustafsson <daniel@yesql.se> writes:
> Looking at this I'm leaning towards paring down the diff posted upthread with
> pretty much this, I think that will provide value while avoid causing
> confusion.
WFM.
> As a related side note, there are four instances of "case insensitive{ly}" in
> the docs with all other instances using "case-insensitive{ly}". I'm inclined
> to fix those four to use a dash while at it to be consistent across all pages.
+1
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2022-08-16 21:49 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-08-16 10:17 Lower or Upper case for F.33. pg_trgm PG Doc comments form <noreply@postgresql.org>
2022-08-16 10:36 ` Daniel Gustafsson <daniel@yesql.se>
2022-08-16 10:54 ` Erik Rijkers <er@xs4all.nl>
2022-08-16 11:46 ` Daniel Gustafsson <daniel@yesql.se>
2022-08-16 12:35 ` Marc M. <marcmaiwald@googlemail.com>
2022-08-16 13:03 ` Erik Rijkers <er@xs4all.nl>
2022-08-16 13:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-08-16 14:16 ` Marc M. <marcmaiwald@googlemail.com>
2022-08-16 21:28 ` Daniel Gustafsson <daniel@yesql.se>
2022-08-16 21:49 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox