Postgres Pro
Downloads
Home   >   mailing lists

Thread: Case Insensitive

Case Insensitive

From
Sridhar N Bamandlapally
Date:
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

From
Sameer Kumar
Date:


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

From
Sameer Kumar
Date:


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

From
Ben Madin
Date:
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



--

Ausvet Logo

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

Re: Case Insensitive

From
Ben Madin
Date:
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



--

Ausvet Logo

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

Re: Case Insensitive

From
Murtuza Zabuawala
Date:
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

From
Murtuza Zabuawala
Date:
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

From
Andreas Kretschmer
Date:

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

From
Ron
Date:
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

From
Steve Atkins
Date:

> 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).

Cheers,
  Steve

Re: Case Insensitive

From
Sameer Kumar
Date:


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

From
Sameer Kumar
Date:


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

From
Sridhar N Bamandlapally
Date:
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

From
Ron
Date:
On 3/28/19 3:33 AM, Steve Atkins 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).

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

From
Peter Eisentraut
Date:
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

From
Peter Eisentraut
Date:
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

From
Steve Atkins
Date:

> On Mar 28, 2019, at 9:08 AM, Ron <ronljohnsonjr@gmail.com> wrote:
>
> On 3/28/19 3:33 AM, Steve Atkins 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).
>
> 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
expressionindex. 

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

Check "\df+ upper"

Cheers,
  Steve

Re: Case Insensitive

From
Andy Anderson
Date:
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



--

Ausvet Logo

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

Attachment

Re: Case Insensitive

From
Andy Anderson
Date:
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



--

Ausvet Logo

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

Re: Case Insensitive

From
Andreas Kretschmer
Date:

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

From
Tim Clarke
Date:
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

From
"Brad Nicholson"
Date:

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.

Re: Case Insensitive

From
Shreeyansh Dba
Date:
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

Re: Case Insensitive

From
Shreeyansh Dba
Date:
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