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 comparisons.
>
> That just begs the
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) isn't the
appropriate A
On 26.11.21 08:37, Jakub Jedelsky wrote:
postgres=# SELECT
postgres-# 'ΣΣ' ILIKE 'σσ' COLLATE "en_US",
postgres-# 'ΣΣ' ILIKE 'σς' COLLATE "en_US"
postgres-# ;
?column? | ?column?
--+--
t | f
(1 row)
postgres=# SELECT
postgres-# 'ΣΣ' ILIKE 'σσ' COLLATE "en-US-x-icu",
po
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
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?
>
Igal Sapir wrote:
> > Out of curiosity is there a eason not to use the citext type for th?
> >
> >
> Using the collation seems like a much cleaner approach, and I trust ICU to
> do a better job at comparing strings according to language rules etc.
One notable difference between citext and
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
> likely the
> > > > collation that I chos
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_insensitive(
Hello,
use correct locale identifier, in your case it should be:
create collation "case_insensitive" (provider=icu,
locale="en-US-u-ks-level2", deterministic = false);
See
http://www.unicode.org/reports/tr35/tr35-collation.html#Setting_Options
for available options.
--
Pavel
Dne 09.10.2019
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=icu, locale='en-US-x-icu'
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
>one (for English/US?). Her
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.
Hi Sridhar,
There are a few workarounds available, hope this will help you.
1) Use the citext extension
2) Use ILIKE instead of LIKE
3) Use Postgres lower() function
4) Add an index on lower(ename)
Thanks & Regards,
*Shreeyansh DBA Team*
www.shreeyansh.com
On Thu, Mar 28, 2019 at 1:50 PM Sridh
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';
Would this also select characters with diacritical marks? For example,
eid | ename
-+---
1 | aaa
2 | AAA
3 | áäâ
4 | āåȧ
— Andy
> On Mar 28, 2019, at 4:26 AM, Ben Madin wrote:
>
> Or you can just use `ilike`:
>
> SELECT * FROM emp WHERE ename ilike 'aaa';
>
> https://www.
> On Mar 28, 2019, at 9:08 AM, Ron wrote:
>
> 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)=upp
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, 24x7 Suppo
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
> test=*# create table emp (eid int,
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, 'aaa');
INSER
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 way
>
> postgres=# select * from emp;
>
Or you can just use `ilike`:
SELECT * FROM emp WHERE ename ilike 'aaa';
https://www.postgresql.org/docs/10/sql-select.html#SQL-WHERE
cheers
Ben
On Thu, 28 Mar 2019 at 16:24, Sameer Kumar wrote:
>
>
> On Thu, 28 Mar, 2019, 4:20 PM Sridhar N Bamandlapally, <
> sridhar@gmail.com> wrote:
>
>
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 mean, need below way
35 matches
Mail list logo
