Re: [GENERAL] Case sensitivity

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

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

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(at)hogranch(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
:o) dev

Can case-insensitive collation help here?

On Wed, Dec 11, 2013 at 4:55 PM, Dev Kumkar <devdas(dot)kumkar(at)gmail(dot)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(at)hogranch(dot)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 ?
>>
>>

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(at)crankycanuck(dot)ca

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(at)crankycanuck(dot)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
>

On Wed, Dec 11, 2013 at 9:47 PM, Dev Kumkar <devdas(dot)kumkar(at)gmail(dot)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?

+ hackers

On Thu, Dec 12, 2013 at 12:34 PM, Dev Kumkar <devdas(dot)kumkar(at)gmail(dot)com>wrote:

> On Wed, Dec 11, 2013 at 9:47 PM, Dev Kumkar <devdas(dot)kumkar(at)gmail(dot)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?
>

On Thu, Dec 12, 2013 at 12:47 PM, Dev Kumkar <devdas(dot)kumkar(at)gmail(dot)com>wrote:

> + hackers
>
>
>
> On Thu, Dec 12, 2013 at 12:34 PM, Dev Kumkar <devdas(dot)kumkar(at)gmail(dot)com>wrote:
>
>> On Wed, Dec 11, 2013 at 9:47 PM, Dev Kumkar <devdas(dot)kumkar(at)gmail(dot)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...