pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCase Insensitive
19+ messages / 13 participants
[nested] [flat]
* Case Insensitive
@ 2019-03-28 08:20 Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
2019-03-28 08:23 ` Re: Case Insensitive Sameer Kumar <sameer.kumar@ashnik.com>
2019-03-28 08:27 ` Re: Case Insensitive Murtuza Zabuawala <murtuza.zabuawala@enterprisedb.com>
2019-03-28 08:28 ` Re: Case Insensitive Andreas Kretschmer <andreas@a-kretschmer.de>
2019-03-28 09:24 ` Re: Case Insensitive Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2019-03-28 11:01 ` Re: Case Insensitive Holger Jakobs <holger@jakobs.com>
2019-03-28 15:37 ` Re: Case Insensitive Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 6 replies; 19+ messages in thread
From: Sridhar N Bamandlapally @ 2019-03-28 08:20 UTC (permalink / raw)
To: PG-General Mailing List <pgsql-general@postgresql.org>; pgsql-admin
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Case Insensitive
2019-03-28 08:20 Case Insensitive Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
@ 2019-03-28 08:23 ` Sameer Kumar <sameer.kumar@ashnik.com>
2019-03-28 08:26 ` Re: Case Insensitive Ben Madin <ben@ausvet.com.au>
2019-03-28 08:29 ` Re: Case Insensitive Ron <ronljohnsonjr@gmail.com>
5 siblings, 2 replies; 19+ messages in thread
From: Sameer Kumar @ 2019-03-28 08:23 UTC (permalink / raw)
To: Sridhar N Bamandlapally <sridhar.bn1@gmail.com>; +Cc: PostgreSQL General Discussion Forum <pgsql-general@postgresql.org>; pgsql-admin
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
>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Case Insensitive
2019-03-28 08:20 Case Insensitive Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
2019-03-28 08:23 ` Re: Case Insensitive Sameer Kumar <sameer.kumar@ashnik.com>
@ 2019-03-28 08:26 ` Ben Madin <ben@ausvet.com.au>
2019-03-28 11:10 ` Re: Case Insensitive Andy Anderson <aanderson@amherst.edu>
1 sibling, 1 reply; 19+ messages in thread
From: Ben Madin @ 2019-03-28 08:26 UTC (permalink / raw)
To: Sameer Kumar <sameer.kumar@ashnik.com>; +Cc: Sridhar N Bamandlapally <sridhar.bn1@gmail.com>; PostgreSQL General Discussion Forum <pgsql-general@postgresql.org>; pgsql-admin
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/;
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Case Insensitive
2019-03-28 08:20 Case Insensitive Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
2019-03-28 08:23 ` Re: Case Insensitive Sameer Kumar <sameer.kumar@ashnik.com>
2019-03-28 08:26 ` Re: Case Insensitive Ben Madin <ben@ausvet.com.au>
@ 2019-03-28 11:10 ` Andy Anderson <aanderson@amherst.edu>
2019-03-28 12:09 ` Re: Case Insensitive Tim Clarke <tim.clarke@minerva.info>
0 siblings, 1 reply; 19+ messages in thread
From: Andy Anderson @ 2019-03-28 11:10 UTC (permalink / raw)
To: PostgreSQL General Discussion Forum <pgsql-general@postgresql.org>; pgsql-admin
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';
>
> https://www.postgresql.org/docs/10/sql-select.html#SQL-WHERE <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 <mailto:sameer.kumar@ashnik.com>> wrote:
>
>
> On Thu, 28 Mar, 2019, 4:20 PM Sridhar N Bamandlapally, <sridhar.bn1@gmail.com <mailto: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
>
>
>
> --
>
> <https://www.ausvet.com.au/;
>
> Dr Ben Madin BVMS MVPHMgmt PhD MANZCVS GAICD
> Managing Director
> Mobile: +61 448 887 220 <tel:+61448887220>
> E-mail: ben@ausvet.com.au <mailto:ben@ausvet.com.au>
> Website: www.ausvet.com.au <https://www.ausvet.com.au/;
> Skype: benmadin <>
> Address: 5 Shuffrey Street
> Fremantle, WA 6160
> Australia
Attachments:
signature.asc (application/pgp-signature, 528B) download
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Case Insensitive
2019-03-28 08:20 Case Insensitive Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
2019-03-28 08:23 ` Re: Case Insensitive Sameer Kumar <sameer.kumar@ashnik.com>
2019-03-28 08:26 ` Re: Case Insensitive Ben Madin <ben@ausvet.com.au>
2019-03-28 11:10 ` Re: Case Insensitive Andy Anderson <aanderson@amherst.edu>
@ 2019-03-28 12:09 ` Tim Clarke <tim.clarke@minerva.info>
0 siblings, 0 replies; 19+ messages in thread
From: Tim Clarke @ 2019-03-28 12:09 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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/; for further information.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Case Insensitive
2019-03-28 08:20 Case Insensitive Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
2019-03-28 08:23 ` Re: Case Insensitive Sameer Kumar <sameer.kumar@ashnik.com>
@ 2019-03-28 08:29 ` Ron <ronljohnsonjr@gmail.com>
2019-03-28 08:33 ` Re: Case Insensitive Steve Atkins <steve@blighty.com>
1 sibling, 1 reply; 19+ messages in thread
From: Ron @ 2019-03-28 08:29 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Case Insensitive
2019-03-28 08:20 Case Insensitive Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
2019-03-28 08:23 ` Re: Case Insensitive Sameer Kumar <sameer.kumar@ashnik.com>
2019-03-28 08:29 ` Re: Case Insensitive Ron <ronljohnsonjr@gmail.com>
@ 2019-03-28 08:33 ` Steve Atkins <steve@blighty.com>
2019-03-28 08:37 ` Re: Case Insensitive Sameer Kumar <sameer.kumar@ashnik.com>
2019-03-28 09:08 ` Re: Case Insensitive Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 19+ messages in thread
From: Steve Atkins @ 2019-03-28 08:33 UTC (permalink / raw)
To: PG mailing List <pgsql-general@lists.postgresql.org>
> 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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Case Insensitive
2019-03-28 08:20 Case Insensitive Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
2019-03-28 08:23 ` Re: Case Insensitive Sameer Kumar <sameer.kumar@ashnik.com>
2019-03-28 08:29 ` Re: Case Insensitive Ron <ronljohnsonjr@gmail.com>
2019-03-28 08:33 ` Re: Case Insensitive Steve Atkins <steve@blighty.com>
@ 2019-03-28 08:37 ` Sameer Kumar <sameer.kumar@ashnik.com>
2019-03-28 09:05 ` Re: Case Insensitive Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
1 sibling, 1 reply; 19+ messages in thread
From: Sameer Kumar @ 2019-03-28 08:37 UTC (permalink / raw)
To: Steve Atkins <steve@blighty.com>; +Cc: PG mailing List <pgsql-general@lists.postgresql.org>
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
>
>
>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Case Insensitive
2019-03-28 08:20 Case Insensitive Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
2019-03-28 08:23 ` Re: Case Insensitive Sameer Kumar <sameer.kumar@ashnik.com>
2019-03-28 08:29 ` Re: Case Insensitive Ron <ronljohnsonjr@gmail.com>
2019-03-28 08:33 ` Re: Case Insensitive Steve Atkins <steve@blighty.com>
2019-03-28 08:37 ` Re: Case Insensitive Sameer Kumar <sameer.kumar@ashnik.com>
@ 2019-03-28 09:05 ` Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
0 siblings, 0 replies; 19+ messages in thread
From: Sridhar N Bamandlapally @ 2019-03-28 09:05 UTC (permalink / raw)
To: Sameer Kumar <sameer.kumar@ashnik.com>; +Cc: Steve Atkins <steve@blighty.com>; PG mailing List <pgsql-general@lists.postgresql.org>
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
>>
>>
>>
>>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Case Insensitive
2019-03-28 08:20 Case Insensitive Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
2019-03-28 08:23 ` Re: Case Insensitive Sameer Kumar <sameer.kumar@ashnik.com>
2019-03-28 08:29 ` Re: Case Insensitive Ron <ronljohnsonjr@gmail.com>
2019-03-28 08:33 ` Re: Case Insensitive Steve Atkins <steve@blighty.com>
@ 2019-03-28 09:08 ` Ron <ronljohnsonjr@gmail.com>
2019-03-28 09:30 ` Re: Case Insensitive Steve Atkins <steve@blighty.com>
1 sibling, 1 reply; 19+ messages in thread
From: Ron @ 2019-03-28 09:08 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Case Insensitive
2019-03-28 08:20 Case Insensitive Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
2019-03-28 08:23 ` Re: Case Insensitive Sameer Kumar <sameer.kumar@ashnik.com>
2019-03-28 08:29 ` Re: Case Insensitive Ron <ronljohnsonjr@gmail.com>
2019-03-28 08:33 ` Re: Case Insensitive Steve Atkins <steve@blighty.com>
2019-03-28 09:08 ` Re: Case Insensitive Ron <ronljohnsonjr@gmail.com>
@ 2019-03-28 09:30 ` Steve Atkins <steve@blighty.com>
0 siblings, 0 replies; 19+ messages in thread
From: Steve Atkins @ 2019-03-28 09:30 UTC (permalink / raw)
To: PG mailing List <pgsql-general@lists.postgresql.org>
> 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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Case Insensitive
2019-03-28 08:20 Case Insensitive Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
@ 2019-03-28 08:27 ` Murtuza Zabuawala <murtuza.zabuawala@enterprisedb.com>
5 siblings, 0 replies; 19+ messages in thread
From: Murtuza Zabuawala @ 2019-03-28 08:27 UTC (permalink / raw)
To: Sridhar N Bamandlapally <sridhar.bn1@gmail.com>; +Cc: PG-General Mailing List <pgsql-general@postgresql.org>; pgsql-admin
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
>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Case Insensitive
2019-03-28 08:20 Case Insensitive Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
@ 2019-03-28 08:28 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2019-03-28 08:33 ` Re: Case Insensitive Sameer Kumar <sameer.kumar@ashnik.com>
5 siblings, 1 reply; 19+ messages in thread
From: Andreas Kretschmer @ 2019-03-28 08:28 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Case Insensitive
2019-03-28 08:20 Case Insensitive Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
2019-03-28 08:28 ` Re: Case Insensitive Andreas Kretschmer <andreas@a-kretschmer.de>
@ 2019-03-28 08:33 ` Sameer Kumar <sameer.kumar@ashnik.com>
2019-03-28 11:28 ` Re: Case Insensitive Andreas Kretschmer <andreas@a-kretschmer.de>
0 siblings, 1 reply; 19+ messages in thread
From: Sameer Kumar @ 2019-03-28 08:33 UTC (permalink / raw)
To: Andreas Kretschmer <andreas@a-kretschmer.de>; +Cc: pgsql-general@lists.postgresql.org
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
>
>
>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Case Insensitive
2019-03-28 08:20 Case Insensitive Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
2019-03-28 08:28 ` Re: Case Insensitive Andreas Kretschmer <andreas@a-kretschmer.de>
2019-03-28 08:33 ` Re: Case Insensitive Sameer Kumar <sameer.kumar@ashnik.com>
@ 2019-03-28 11:28 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2019-03-28 12:38 ` Re: Case Insensitive Brad Nicholson <bradn@ca.ibm.com>
0 siblings, 1 reply; 19+ messages in thread
From: Andreas Kretschmer @ 2019-03-28 11:28 UTC (permalink / raw)
To: Sameer Kumar <sameer.kumar@ashnik.com>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Case Insensitive
2019-03-28 08:20 Case Insensitive Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
2019-03-28 08:28 ` Re: Case Insensitive Andreas Kretschmer <andreas@a-kretschmer.de>
2019-03-28 08:33 ` Re: Case Insensitive Sameer Kumar <sameer.kumar@ashnik.com>
2019-03-28 11:28 ` Re: Case Insensitive Andreas Kretschmer <andreas@a-kretschmer.de>
@ 2019-03-28 12:38 ` Brad Nicholson <bradn@ca.ibm.com>
0 siblings, 0 replies; 19+ messages in thread
From: Brad Nicholson @ 2019-03-28 12:38 UTC (permalink / raw)
To: Andreas Kretschmer <andreas@a-kretschmer.de>; +Cc: pgsql-general@lists.postgresql.org, Sameer Kumar <sameer.kumar@ashnik.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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Case Insensitive
2019-03-28 08:20 Case Insensitive Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
@ 2019-03-28 09:24 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
5 siblings, 0 replies; 19+ messages in thread
From: Peter Eisentraut @ 2019-03-28 09:24 UTC (permalink / raw)
To: Sridhar N Bamandlapally <sridhar.bn1@gmail.com>; PG-General Mailing List <pgsql-general@postgresql.org>; pgsql-admin
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Case Insensitive
2019-03-28 08:20 Case Insensitive Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
@ 2019-03-28 11:01 ` Holger Jakobs <holger@jakobs.com>
5 siblings, 0 replies; 19+ messages in thread
From: Holger Jakobs @ 2019-03-28 11:01 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Case Insensitive
2019-03-28 08:20 Case Insensitive Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
@ 2019-03-28 15:37 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
5 siblings, 0 replies; 19+ messages in thread
From: Shreeyansh Dba @ 2019-03-28 15:37 UTC (permalink / raw)
To: Sridhar N Bamandlapally <sridhar.bn1@gmail.com>; +Cc: PG-General Mailing List <pgsql-general@postgresql.org>; pgsql-admin
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
>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2019-03-28 15:37 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-03-28 08:20 Case Insensitive Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
2019-03-28 08:23 ` Sameer Kumar <sameer.kumar@ashnik.com>
2019-03-28 08:26 ` Ben Madin <ben@ausvet.com.au>
2019-03-28 11:10 ` Andy Anderson <aanderson@amherst.edu>
2019-03-28 12:09 ` Tim Clarke <tim.clarke@minerva.info>
2019-03-28 08:29 ` Ron <ronljohnsonjr@gmail.com>
2019-03-28 08:33 ` Steve Atkins <steve@blighty.com>
2019-03-28 08:37 ` Sameer Kumar <sameer.kumar@ashnik.com>
2019-03-28 09:05 ` Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
2019-03-28 09:08 ` Ron <ronljohnsonjr@gmail.com>
2019-03-28 09:30 ` Steve Atkins <steve@blighty.com>
2019-03-28 08:27 ` Murtuza Zabuawala <murtuza.zabuawala@enterprisedb.com>
2019-03-28 08:28 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2019-03-28 08:33 ` Sameer Kumar <sameer.kumar@ashnik.com>
2019-03-28 11:28 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2019-03-28 12:38 ` Brad Nicholson <bradn@ca.ibm.com>
2019-03-28 09:24 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2019-03-28 11:01 ` Holger Jakobs <holger@jakobs.com>
2019-03-28 15:37 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox