I realise this may not be applicable to the original problem, but
non-deterministic collations seems to offer a solution::
dakkar@[local] dakkar=> create collation "en-US-ins-icu" (
provider=icu,
locale='en-US-u-ks-level2',
deterministic=false
);
dakkar@[l
On Wed, Dec 1, 2021 at 8:49 PM Tom Lane wrote:
> Peter Eisentraut writes:
> > Running lower() like this is really the wrong thing to do. We should be
> > doing "case folding" instead, which normalizes these differences for the
> > purpose of case-insensitive comp
Peter Eisentraut writes:
> Running lower() like this is really the wrong thing to do. We should be
> doing "case folding" instead, which normalizes these differences for the
> purpose of case-insensitive comparisons.
That just begs the question: if tolower (or towlower) is
ally does lower(a) LIKE lower(b), and
select lower('ΣΣ' COLLATE "en_US"), lower('ΣΣ' COLLATE "en-US-x-icu");
lower | lower
---+---
σσ| σς
Running lower() like this is really the wrong thing to do. We should be
doing "case folding" instead, which normalizes these differences for the
purpose of case-insensitive comparisons.
Am 26.11.21 um 08:37 schrieb Jakub Jedelsky:
Hello,
during our tests of Postgres with ICU we found an issue with ILIKE of
upper and lowercase sigma (Σ). The letter has two lowercase variants σ
and ς (at the end of a word). I'm working with en_US and en-US-x-icu
collations and results are a bi
Jakub Jedelsky writes:
> during our tests of Postgres with ICU we found an issue with ILIKE of upper
> and lowercase sigma (Σ). The letter has two lowercase variants σ and ς (at
> the end of a word). I'm working with en_US and en-US-x-icu collations and
> results are a bit unexpected - they are in
On 26/11/21 9:37 π.μ., Jakub Jedelsky wrote:
Hello,
Thank you for dealing with Greek!
during our tests of Postgres with ICU we found an issue with ILIKE of upper and lowercase sigma (Σ). The letter has two lowercase variants σ and ς (at the end of a word). I'm working with en_US and
en-US-x-i
Hello,
during our tests of Postgres with ICU we found an issue with ILIKE of upper
and lowercase sigma (Σ). The letter has two lowercase variants σ and ς (at
the end of a word). I'm working with en_US and en-US-x-icu collations and
results are a bit unexpected - they are inverted:
postgres=# SELE
Thanks a lot Michael . Concurrent index building solves my problem of
building index without locking.
Best Regards,
Saurav
On Thu, Aug 13, 2020 at 10:45 PM Michael Lewis wrote:
> create index concurrently lower( jsonb->>'name' ), drop old_index
> concurrently, ensure that where/on/group by cond
create index concurrently lower( jsonb->>'name' ), drop old_index
concurrently, ensure that where/on/group by conditions use lower(
jsonb->>'name' ), then take lunch.
What's your concern with this process?
>
Hi All,
We use PostgreSQL JSONB storage.
One of the keys in the JSON will be always have a "name" key.
We want to perform case insensitive query on the name key.
Our application is already live so we have some data and have existing
indexes on the name key.
I understand that one o
ifference between citext and case-insensitive collations
by ICU is that the latter recognizes canonically equivalent sequences
of codepoints [1] as equal, while the former does not.
For instance:
=# CREATE COLLATION ci (locale='und@colStrength=secondary',
provider='icu', d
On Fri, Oct 11, 2019 at 1:09 AM stan wrote:
> On Thu, Oct 10, 2019 at 05:41:47AM -0700, Igal @ Lucee.org wrote:
> > On 10/9/2019 12:34 AM, Laurenz Albe wrote:
> > > Igal Sapir wrote:
> > > > I am trying to test a simple case insensitive comparison. Most
> li
Thomas,
On 10/10/2019 6:22 AM, Thomas Kellerer wrote:
Igal @ Lucee.org schrieb am 10.10.2019 um 14:41:
Thank you all for replying. I tried to use the locale suggested by
both Laurenz and Pavel, en-US-u-ks-level2, but I'm still getting
false for a simple comparison of 'Abc' = 'abc'. I tried t
Igal @ Lucee.org schrieb am 10.10.2019 um 14:41:
> Thank you all for replying. I tried to use the locale suggested by
> both Laurenz and Pavel, en-US-u-ks-level2, but I'm still getting
> false for a simple comparison of 'Abc' = 'abc'. I tried the locale
> both as a 'string' and as an "identifier"
On 10/9/2019 12:34 AM, Laurenz Albe wrote:
Igal Sapir wrote:
I am trying to test a simple case insensitive comparison. Most likely the
collation that I chose is wrong, but I'm not sure how to choose the correct
one (for English/US?). Here is my snippet:
create collation case_insens
avel
Dne 09.10.2019 0:51:52, "Igal Sapir" napsal:
I am trying to test a simple case insensitive comparison. Most likely
the collation that I chose is wrong, but I'm not sure how to choose the
correct one (for English/US?). Here is my snippet:
create collation case_insensitive
Igal Sapir wrote:
> I am trying to test a simple case insensitive comparison. Most likely the
> collation that I chose is wrong, but I'm not sure how to choose the correct
> one (for English/US?). Here is my snippet:
>
> create collation case_insensitive(
> provider=i
Using the datatype citext might be an alternative solution
Igal Sapir schreef op October 8, 2019 10:51:52 PM UTC:
>I am trying to test a simple case insensitive comparison. Most likely
>the
>collation that I chose is wrong, but I'm not sure how to choose the
>correct
>
As I understand it, custom collation are not applied globally. Meaning, you
have to associate a collation with a column or en expression with COLLATE.
I am trying to test a simple case insensitive comparison. Most likely the
collation that I chose is wrong, but I'm not sure how to choose the correct
one (for English/US?). Here is my snippet:
create collation case_insensitive(
provider=icu, locale='en-US-x-icu', dete
0 PM Sridhar N Bamandlapally <
sridhar@gmail.com> wrote:
> Hi PG-General and Pgsql-Admin
>
> Can we achieve CASE INSENSITIVE in PostgreSQL?
>
> I mean, need below way
>
> postgres=# select * from emp;
> eid | ename
> -+---
>1 | aaa
>2 | AAA
&
Andreas Kretschmer wrote on 03/28/2019 07:28:53
AM:
> >
> > Ummm... Will it use an index (a BTree index)?
> >
>
> test=# explain select * from emp where ename = 'aaa';
> QUERY PLAN
> -
> Seq Scan on emp (cost=0.00..25.
On 28/03/2019 11:10, Andy Anderson wrote:
Would this also select characters with diacritical marks? For example,
eid | ename
-+---
1 | aaa
2 | AAA
3 | áäâ
4 | āåȧ
— Andy
For that you need https://www.postgresql.org/docs/10/unaccent.html
Tim Clarke
Main: +44 (0)1376 503
Am 28.03.19 um 09:33 schrieb Sameer Kumar:
test=*# select * from emp where ename = 'aaa';
eid | ename
-+---
1 | aaa
2 | AAA
(2 rows)
Ummm... Will it use an index (a BTree index)?
test=# explain select * from emp where ename = 'aaa';
rote:
>
>
> On Thu, 28 Mar, 2019, 4:20 PM Sridhar N Bamandlapally, <mailto:sridhar....@gmail.com>> wrote:
> Hi PG-General and Pgsql-Admin
>
> Can we achieve CASE INSENSITIVE in PostgreSQL?
>
> You are perhaps migrating from another RDBMS where this kind of feature is
upper function:
>>>>
>>>> select * from emp where upper(ename)=upper('aaa');
>>> That's a guaranteed table scan.
>> Unless you have an index on upper(ename).
>
> Are you sure? I thought the lpart had to be immutable for the query
> opt
On 2019-03-28 09:20, Sridhar N Bamandlapally wrote:
> Can we achieve CASE INSENSITIVE in PostgreSQL?
Use the citext extension.
In PostgreSQL 12, there will be support for case-insensitive collations.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 2
On 3/28/19 3:33 AM, Steve Atkins wrote:
On Mar 28, 2019, at 8:29 AM, Ron wrote:
On 3/28/19 3:23 AM, Sameer Kumar wrote:
[snip]
You can write a query with upper function:
select * from emp where upper(ename)=upper('aaa');
That's a guaranteed table scan.
Unless you have an index on upper(en
With ILIKE or extension CITEXT, does it have any impact on Indexes, like
not picking index ?
ILIKE works only for operator LIKE not for operator =
CITEXT seems some possibilities,
Thanks
Sridhar
On Thu, Mar 28, 2019 at 2:07 PM Sameer Kumar
wrote:
>
>
> On Thu, 28 Mar, 2019, 4:33 PM Steve Atk
On Thu, 28 Mar, 2019, 4:33 PM Steve Atkins, wrote:
>
>
> > On Mar 28, 2019, at 8:29 AM, Ron wrote:
> >
> > On 3/28/19 3:23 AM, Sameer Kumar wrote:
> > [snip]
> >> You can write a query with upper function:
> >>
> >> select * from emp where upper(ename)=upper('aaa');
> >
> > That's a guaranteed t
> On Mar 28, 2019, at 8:29 AM, Ron wrote:
>
> On 3/28/19 3:23 AM, Sameer Kumar wrote:
> [snip]
>> You can write a query with upper function:
>>
>> select * from emp where upper(ename)=upper('aaa');
>
> That's a guaranteed table scan.
Unless you have an index on upper(ename).
Cheers,
Stev
On Thu, 28 Mar, 2019, 4:28 PM Andreas Kretschmer,
wrote:
>
>
> Am 28.03.19 um 09:20 schrieb Sridhar N Bamandlapally:
> > Hi PG-General and Pgsql-Admin
> >
> > Can we achieve CASE INSENSITIVE in PostgreSQL?
>
> test=# create extension citext;
> CREATE EXTENSION
On 3/28/19 3:23 AM, Sameer Kumar wrote:
[snip]
You can write a query with upper function:
select * from emp where upper(ename)=upper('aaa');
That's a guaranteed table scan.
--
Angular momentum makes the world go 'round.
Am 28.03.19 um 09:20 schrieb Sridhar N Bamandlapally:
Hi PG-General and Pgsql-Admin
Can we achieve CASE INSENSITIVE in PostgreSQL?
test=# create extension citext;
CREATE EXTENSION
test=*# create table emp (eid int, ename citext);
CREATE TABLE
test=*# insert into emp values (1,
On Thu, Mar 28, 2019 at 1:50 PM Sridhar N Bamandlapally <
sridhar@gmail.com> wrote:
> Hi PG-General and Pgsql-Admin
>
> Can we achieve CASE INSENSITIVE in PostgreSQL?
>
You can try
*select * from emp where ename *ILIKE *'aaa';*
>
> I mean, need below wa
dhar@gmail.com> wrote:
>
>> Hi PG-General and Pgsql-Admin
>>
>> Can we achieve CASE INSENSITIVE in PostgreSQL?
>>
>
> You are perhaps migrating from another RDBMS where this kind of feature is
> considered a feature.
>
>
>
>> I mean, need below w
On Thu, 28 Mar, 2019, 4:20 PM Sridhar N Bamandlapally, <
sridhar@gmail.com> wrote:
> Hi PG-General and Pgsql-Admin
>
> Can we achieve CASE INSENSITIVE in PostgreSQL?
>
You are perhaps migrating from another RDBMS where this kind of feature is
considered a feature.
> I
Hi PG-General and Pgsql-Admin
Can we achieve CASE INSENSITIVE in PostgreSQL?
I mean, need below way
postgres=# select * from emp;
eid | ename
-+---
1 | aaa
2 | AAA
(2 rows)
*postgres=# select * from emp where ename='aaa';*
* eid | ename*
*-+---*
* 1 |
39 matches
Mail list logo
