pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCase sensitivity
9+ messages / 3 participants
[nested] [flat]
* Case sensitivity
@ 2013-12-11 06:31 Dev Kumkar <devdas.kumkar@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Dev Kumkar @ 2013-12-11 06:31 UTC (permalink / raw)
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
How to create case insensitive database?
I know about CITEXT data type, but what am looking for is if there any
parameter at database level which just makes the database case insensitive.
I mean both values 'ABC' and 'abc' are treated same for inserts and also
all the comparisons by default are case insensitive.
Was not able to find anything concrete forums, hope I didn't overlook
anything here.
Regards...
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Case sensitivity
@ 2013-12-11 06:40 John R Pierce <pierce@hogranch.com>
parent: Dev Kumkar <devdas.kumkar@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: John R Pierce @ 2013-12-11 06:40 UTC (permalink / raw)
To: pgsql-general@postgresql.org
On 12/10/2013 10:31 PM, Dev Kumkar wrote:
> I know about CITEXT data type, but what am looking for is if there any
> parameter at database level which just makes the database case
> insensitive.
there's nothing that will do that in postgres.
whats wrong with using CITEXT ?
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Case sensitivity
@ 2013-12-11 11:25 Dev Kumkar <devdas.kumkar@gmail.com>
parent: John R Pierce <pierce@hogranch.com>
0 siblings, 2 replies; 9+ messages in thread
From: Dev Kumkar @ 2013-12-11 11:25 UTC (permalink / raw)
To: John R Pierce <pierce@hogranch.com>; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Thanks John.
Yes CITEXT would work, the only thing its needs DDL changes across and
hence was looking for any such global database parameter setting while
creating database. I have been looking at other discussions and doesn't
look like anything of that coming up soon that makes database case
insensitive.
regards...
On Wed, Dec 11, 2013 at 12:10 PM, John R Pierce <pierce@hogranch.com> wrote:
> On 12/10/2013 10:31 PM, Dev Kumkar wrote:
>
>> I know about CITEXT data type, but what am looking for is if there any
>> parameter at database level which just makes the database case insensitive.
>>
>
> there's nothing that will do that in postgres.
>
> whats wrong with using CITEXT ?
>
>
>
> --
> john r pierce 37N 122W
> somewhere on the middle of the left coast
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
:o) dev
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Case sensitivity
@ 2013-12-11 15:16 Dev Kumkar <devdas.kumkar@gmail.com>
parent: Dev Kumkar <devdas.kumkar@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Dev Kumkar @ 2013-12-11 15:16 UTC (permalink / raw)
To: John R Pierce <pierce@hogranch.com>; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Can case-insensitive collation help here?
On Wed, Dec 11, 2013 at 4:55 PM, Dev Kumkar <devdas.kumkar@gmail.com> wrote:
> Thanks John.
>
> Yes CITEXT would work, the only thing its needs DDL changes across and
> hence was looking for any such global database parameter setting while
> creating database. I have been looking at other discussions and doesn't
> look like anything of that coming up soon that makes database case
> insensitive.
>
> regards...
>
>
> On Wed, Dec 11, 2013 at 12:10 PM, John R Pierce <pierce@hogranch.com>wrote:
>
>> On 12/10/2013 10:31 PM, Dev Kumkar wrote:
>>
>>> I know about CITEXT data type, but what am looking for is if there any
>>> parameter at database level which just makes the database case insensitive.
>>>
>>
>> there's nothing that will do that in postgres.
>>
>> whats wrong with using CITEXT ?
>>
>>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Case sensitivity
@ 2013-12-11 15:28 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Dev Kumkar <devdas.kumkar@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Andrew Sullivan @ 2013-12-11 15:28 UTC (permalink / raw)
To: pgsql-general@postgresql.org
On Wed, Dec 11, 2013 at 04:55:07PM +0530, Dev Kumkar wrote:
> creating database. I have been looking at other discussions and doesn't
> look like anything of that coming up soon that makes database case
> insensitive.
You could build lower() indexes on any column you want to search CI
and lower() all the input text during searches, in order to avoid any
work on the schema. Bit of a kludge, though.
Best,
A
--
Andrew Sullivan
ajs@crankycanuck.ca
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Case sensitivity
@ 2013-12-11 16:17 Dev Kumkar <devdas.kumkar@gmail.com>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 9+ messages in thread
From: Dev Kumkar @ 2013-12-11 16:17 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Actually for searches lower will work.
But the other important aspect is 'inserts' which would result 2 rows if
the values are 'A' and 'a'. Intent here to have it case insensitive.
If CITEXT it will update the same row and works.
CITEXT is an alternative but was wondering if there is any other alternate
solution/setting while creating database.
Also does CITEXT fetch via JDBC works the same way as fetch/set string
values? Any quick comments here.
http://techie-experience.blogspot.in/2013/04/hibernate-supporting-case-insensitive.html
Regards...
On Wed, Dec 11, 2013 at 8:58 PM, Andrew Sullivan <ajs@crankycanuck.ca>wrote:
> On Wed, Dec 11, 2013 at 04:55:07PM +0530, Dev Kumkar wrote:
> You could build lower() indexes on any column you want to search CI
> and lower() all the input text during searches, in order to avoid any
> work on the schema. Bit of a kludge, though.
>
> Best,
>
> A
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Case sensitivity
@ 2013-12-12 07:04 Dev Kumkar <devdas.kumkar@gmail.com>
parent: Dev Kumkar <devdas.kumkar@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Dev Kumkar @ 2013-12-12 07:04 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
On Wed, Dec 11, 2013 at 9:47 PM, Dev Kumkar <devdas.kumkar@gmail.com> wrote:
> Actually for searches lower will work.
> But the other important aspect is 'inserts' which would result 2 rows if
> the values are 'A' and 'a'. Intent here to have it case insensitive.
>
> If CITEXT it will update the same row and works.
> CITEXT is an alternative but was wondering if there is any other alternate
> solution/setting while creating database.
>
> Also does CITEXT fetch via JDBC works the same way as fetch/set string
> values? Any quick comments here.
>
> http://techie-experience.blogspot.in/2013/04/hibernate-supporting-case-insensitive.html
>
> Regards...
>
Also if the key columns are CITEXT is there any performance issues on
indexes?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Case sensitivity
@ 2013-12-12 07:17 Dev Kumkar <devdas.kumkar@gmail.com>
parent: Dev Kumkar <devdas.kumkar@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Dev Kumkar @ 2013-12-12 07:17 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-hackers <pgsql-hackers@postgresql.org>; John Pierce <pierce@hogranch.com>
+ hackers
On Thu, Dec 12, 2013 at 12:34 PM, Dev Kumkar <devdas.kumkar@gmail.com>wrote:
> On Wed, Dec 11, 2013 at 9:47 PM, Dev Kumkar <devdas.kumkar@gmail.com>wrote:
>
>> Actually for searches lower will work.
>> But the other important aspect is 'inserts' which would result 2 rows if
>> the values are 'A' and 'a'. Intent here to have it case insensitive.
>>
>> If CITEXT it will update the same row and works.
>> CITEXT is an alternative but was wondering if there is any other
>> alternate solution/setting while creating database.
>>
>> Also does CITEXT fetch via JDBC works the same way as fetch/set string
>> values? Any quick comments here.
>>
>> http://techie-experience.blogspot.in/2013/04/hibernate-supporting-case-insensitive.html
>>
>> Regards...
>>
>
> Also if the key columns are CITEXT is there any performance issues on
> indexes?
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [GENERAL] Case sensitivity
@ 2013-12-13 07:08 Dev Kumkar <devdas.kumkar@gmail.com>
parent: Dev Kumkar <devdas.kumkar@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Dev Kumkar @ 2013-12-13 07:08 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-admin; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-hackers <pgsql-hackers@postgresql.org>; John Pierce <pierce@hogranch.com>
On Thu, Dec 12, 2013 at 12:47 PM, Dev Kumkar <devdas.kumkar@gmail.com>wrote:
> + hackers
>
>
>
> On Thu, Dec 12, 2013 at 12:34 PM, Dev Kumkar <devdas.kumkar@gmail.com>wrote:
>
>> On Wed, Dec 11, 2013 at 9:47 PM, Dev Kumkar <devdas.kumkar@gmail.com>wrote:
>>
>>> Actually for searches lower will work.
>>> But the other important aspect is 'inserts' which would result 2 rows if
>>> the values are 'A' and 'a'. Intent here to have it case insensitive.
>>>
>>> If CITEXT it will update the same row and works.
>>> CITEXT is an alternative but was wondering if there is any other
>>> alternate solution/setting while creating database.
>>>
>>> Also does CITEXT fetch via JDBC works the same way as fetch/set string
>>> values? Any quick comments here.
>>>
>>> http://techie-experience.blogspot.in/2013/04/hibernate-supporting-case-insensitive.html
>>>
>>> Regards...
>>>
>>
>> Also if the key columns are CITEXT is there any performance issues on
>> indexes?
>>
> I am ok with CITEXT but for changing the database design for the
primary/foreign key columns to be CITEXT need some suggestions/comments
regarding performance for inserts/reads.
Awaiting...
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2013-12-13 07:08 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-12-11 06:31 Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-11 06:40 ` John R Pierce <pierce@hogranch.com>
2013-12-11 11:25 ` Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-11 15:16 ` Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-11 15:28 ` Andrew Sullivan <ajs@crankycanuck.ca>
2013-12-11 16:17 ` Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-12 07:04 ` Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-12 07:17 ` Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-13 07:08 ` Dev Kumkar <devdas.kumkar@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