Case Insensitive

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.

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

--

[image: Ausvet Logo] <https://www.ausvet.com.au/&gt;

Dr Ben Madin

BVMS MVPHMgmt PhD MANZCVS GAICD
Managing Director
Mobile:
+61 448 887 220 <+61448887220>
E-mail:
ben@ausvet.com.au
Website:
www.ausvet.com.au
Skype: benmadin
Address:
5 Shuffrey Street
Fremantle, WA 6160
Australia

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';*

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

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.

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

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

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.

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

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

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

Yes, you can use the data type citext

https://www.postgresql.org/docs/current/citext.html

If you don't want to do this, you will have to compare /where
lower(ename)='aaa'/

/
/

Am 28.03.19 um 09:20 schrieb Sridhar N Bamandlapally:

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

--

Holger Jakobs, Bergisch Gladbach
instant messaging: xmpp:holger@jakobs.com
+49 178 9759012 <tel:+491789759012> oder +49 2202 817157
<tel:+492202817157>

Would this also select characters with diacritical marks? For example,

eid | ename
-----+-------
1 | aaa
2 | AAA
3 | áäâ
4 | āåȧ

— Andy

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

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<mailto: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 >><https://www.manifest.co.uk/legal/&gt; for further information.

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.

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: