Re: Case Insensitive

Поиск
Список
Период
Сортировка
Искать

Re: Case Insensitive

От:
Steve Atkins <steve@blighty.com>
Дата:


> 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,
  Steve

Re: Case Insensitive

От:
Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Дата:
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 Support, Remote DBA, Training & Services

Re: Case Insensitive

От:
Andreas Kretschmer <andreas@a-kretschmer.de>
Дата:


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';
                      QUERY PLAN
-----------------------------------------------------
  Seq Scan on emp  (cost=0.00..25.88 rows=6 width=36)
    Filter: (ename = 'aaa'::citext)
(2 rows)

test=*# set enable_seqscan to off;
SET
test=*# explain select * from emp where ename = 'aaa';
                                QUERY PLAN
------------------------------------------------------------------------
  Seq Scan on emp  (cost=10000000000.00..10000000025.88 rows=6 width=36)
    Filter: (ename = 'aaa'::citext)
(2 rows)

test=*# create index emp_ename on emp(ename);
CREATE INDEX
test=*# explain select * from emp where ename = 'aaa';
                               QUERY PLAN
----------------------------------------------------------------------
  Index Scan using emp_ename on emp  (cost=0.13..8.14 rows=1 width=36)
    Index Cond: (ename = 'aaa'::citext)
(2 rows)

test=*#


Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: Case Insensitive

От:
Steve Atkins <steve@blighty.com>
Дата:


> 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)=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 optimizer to decide to use an index (and upper(ename) is mutable).

Yeah. Case insensitive searches like this are pretty much the first example given for why you might want to use an expression index.

The expression in an expression index has to be immutable, but upper() is - it will always give the same output for a given input. (For values of "always" that probably depend on not performing major surgery on collations, but that falls into the "lie to the planner, get rotten results" category).

Check "\df+ upper"

Cheers,
  Steve

Re: Case Insensitive

От:
Tim Clarke <tim.clarke@minerva.info>
Дата:
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 503500 | Fax: +44 (0)1376 503550
Web: https://www.manifest.co.uk/

 

Minerva Analytics Ltd
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | England

----------------------------------------------------------------------------------------------------------------------------

Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to admin@minerva.info
Legal:  Minerva Analytics is the trading name of: Minerva Analytics
Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here >> for further information.

Re: Case Insensitive

От:
Andy Anderson <aanderson@amherst.edu>
Дата:
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 <ben@ausvet.com.au> wrote:

Or you can just use `ilike`:

SELECT * FROM emp WHERE ename ilike 'aaa';


cheers

Ben

On Thu, 28 Mar 2019 at 16:24, Sameer Kumar <sameer.kumar@ashnik.com> wrote:


On Thu, 28 Mar, 2019, 4:20 PM Sridhar N Bamandlapally, <sridhar.bn1@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

postgres=# select * from emp;
 eid | ename
-----+-------
   1 | aaa
   2 | AAA
(2 rows)


postgres=# select * from emp where ename='aaa';
 eid | ename
-----+-------
   1 | aaa
   2 | AAA
(2 rows)
--above result is just an manual made example only


You can write a query with upper function:

select * from emp where upper(ename)=upper('aaa');

Or you can overload the "=" operator for text arguements.



Thanks
Sridhar



--



Dr Ben Madin
 
BVMS MVPHMgmt PhD MANZCVS GAICD
Managing Director
Skype: benmadin
Address: 
5 Shuffrey Street
Fremantle, WA 6160
Australia

Re: Case Insensitive

От:
Andreas Kretschmer <andreas@a-kretschmer.de>
Дата:


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');
INSERT 0 1
test=*# insert into emp values (2, 'AAA');
INSERT 0 1
test=*# select * from emp where ename = 'aaa';
  eid | ename
-----+-------
    1 | aaa
    2 | AAA
(2 rows)


Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: Case Insensitive

От:
Ron <ronljohnsonjr@gmail.com>
Дата:
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(ename).

Are you sure?  I thought the lpart had to be immutable for the query 
optimizer to decide to use an index (and upper(ename) is mutable).

-- 
Angular momentum makes the world go 'round.

Re: Case Insensitive

От:
Ron <ronljohnsonjr@gmail.com>
Дата:
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.

Re: Case Insensitive

От:
Ben Madin <ben@ausvet.com.au>
Дата:
Or you can just use `ilike`:

SELECT * FROM emp WHERE ename ilike 'aaa';


cheers

Ben

On Thu, 28 Mar 2019 at 16:24, Sameer Kumar <sameer.kumar@ashnik.com> wrote:


On Thu, 28 Mar, 2019, 4:20 PM Sridhar N Bamandlapally, <sridhar.bn1@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

postgres=# select * from emp;
 eid | ename
-----+-------
   1 | aaa
   2 | AAA
(2 rows)


postgres=# select * from emp where ename='aaa';
 eid | ename
-----+-------
   1 | aaa
   2 | AAA
(2 rows)
--above result is just an manual made example only


You can write a query with upper function:

select * from emp where upper(ename)=upper('aaa');

Or you can overload the "=" operator for text arguements.



Thanks
Sridhar



--



Dr Ben Madin
 
BVMS MVPHMgmt PhD MANZCVS GAICD
Managing Director
Skype: benmadin
Address: 
5 Shuffrey Street
Fremantle, WA 6160
Australia

Re: Case Insensitive

От:
Sameer Kumar <sameer.kumar@ashnik.com>
Дата:


On Thu, 28 Mar, 2019, 4:20 PM Sridhar N Bamandlapally, <sridhar.bn1@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

postgres=# select * from emp;
 eid | ename
-----+-------
   1 | aaa
   2 | AAA
(2 rows)


postgres=# select * from emp where ename='aaa';
 eid | ename
-----+-------
   1 | aaa
   2 | AAA
(2 rows)
--above result is just an manual made example only


You can write a query with upper function:

select * from emp where upper(ename)=upper('aaa');

Or you can overload the "=" operator for text arguements.



Thanks
Sridhar

Re: Case Insensitive

От:
Sameer Kumar <sameer.kumar@ashnik.com>
Дата:


On Thu, 28 Mar, 2019, 4:33 PM Steve Atkins, <steve@blighty.com> wrote:


> On Mar 28, 2019, at 8:29 AM, Ron <ronljohnsonjr@gmail.com> 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).

Yup, with this or overloading "=", one can create an expression based index and benefit from it.


Cheers,
  Steve



Re: Case Insensitive

От:
Sameer Kumar <sameer.kumar@ashnik.com>
Дата:


On Thu, 28 Mar, 2019, 4:28 PM Andreas Kretschmer, <andreas@a-kretschmer.de> 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, ename citext);
CREATE TABLE
test=*# insert into emp values (1, 'aaa');
INSERT 0 1
test=*# insert into emp values (2, 'AAA');
INSERT 0 1
test=*# select * from emp where ename = 'aaa';
  eid | ename
-----+-------
    1 | aaa
    2 | AAA
(2 rows)


Ummm... Will it use an index (a BTree index)?



Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com



Re: Case Insensitive

От:
Shreeyansh Dba <shreeyansh2014@gmail.com>
Дата:
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 Sridhar N Bamandlapally <sridhar.bn1@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
(2 rows)


postgres=# select * from emp where ename='aaa';
 eid | ename
-----+-------
   1 | aaa
   2 | AAA
(2 rows)
--above result is just an manual made example only


Thanks
Sridhar

Case Insensitive

От:
Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
Дата:
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 | aaa
   2 | AAA
(2 rows)
--above result is just an manual made example only


Thanks
Sridhar

Re: Case Insensitive

От:
Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
Дата:
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 <sameer.kumar@ashnik.com> wrote:


On Thu, 28 Mar, 2019, 4:33 PM Steve Atkins, <steve@blighty.com> wrote:


> On Mar 28, 2019, at 8:29 AM, Ron <ronljohnsonjr@gmail.com> 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).

Yup, with this or overloading "=", one can create an expression based index and benefit from it.


Cheers,
  Steve



Re: Case Insensitive

От:
Murtuza Zabuawala <murtuza.zabuawala@enterprisedb.com>
Дата:
On Thu, Mar 28, 2019 at 1:50 PM Sridhar N Bamandlapally <sridhar.bn1@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;
 eid | ename
-----+-------
   1 | aaa
   2 | AAA
(2 rows)


postgres=# select * from emp where ename='aaa';
 eid | ename
-----+-------
   1 | aaa
   2 | AAA
(2 rows)
--above result is just an manual made example only


Thanks
Sridhar

Re: Case Insensitive

От:
"Brad Nicholson" <bradn@ca.ibm.com>
Дата:

Andreas Kretschmer <andreas@a-kretschmer.de> 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.88 rows=6 width=36)
>     Filter: (ename = 'aaa'::citext)
> (2 rows)
>
> test=*# set enable_seqscan to off;
> SET
> test=*# explain select * from emp where ename = 'aaa';
>                                 QUERY PLAN
> ------------------------------------------------------------------------
>   Seq Scan on emp  (cost=10000000000.00..10000000025.88 rows=6 width=36)
>     Filter: (ename = 'aaa'::citext)
> (2 rows)
>
> test=*# create index emp_ename on emp(ename);
> CREATE INDEX
> test=*# explain select * from emp where ename = 'aaa';
>                                QUERY PLAN
> ----------------------------------------------------------------------
>   Index Scan using emp_ename on emp  (cost=0.13..8.14 rows=1 width=36)
>     Index Cond: (ename = 'aaa'::citext)
> (2 rows)


I used citext heavily in a past project and was quite happy with it.  It was never a source of performance issues.

Brad.

FAQ