pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedCase sensitivity
80+ messages / 48 participants
[nested] [flat]
* Case sensitivity
@ 1999-09-20 03:06 Dana Powers <powersdp@ctrvax.Vanderbilt.Edu>
0 siblings, 0 replies; 80+ messages in thread
From: Dana Powers @ 1999-09-20 03:06 UTC (permalink / raw)
To: pgsql-general
I am evaluating whether it is possible to migrate our Contact manager
system to Postgres on Linux, but I am having difficulty because the
client software was most moronically programmed to access tables using
wildly different case structures, i.e. a table might be created
'MYTABLE', and accessed 'mytable', 'MyTable' or 'MYTABLE' depending on
the context. Is there anyway to disable case-sensitivity in this case,
either through Postgres config, or the ODBC drivers? I have no access to
re-configure the clients, due to their wonderfully closed sourcedness.
Thanks
Dana Powers
dana@fphome.com
^ permalink raw reply [nested|flat] 80+ messages in thread
* Case sensitivity
@ 1999-09-20 03:43 Dana Powers <powersdp@ctrvax.Vanderbilt.Edu>
1999-09-20 04:05 ` Re: [GENERAL] Case sensitivity Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 1 reply; 80+ messages in thread
From: Dana Powers @ 1999-09-20 03:43 UTC (permalink / raw)
To: pgsql-general@hub.org
I am evaluating whether it is possible to migrate our Contact manager
system to Postgres on Linux, but I am having difficulty because the
client software was most moronically programmed to access tables using
wildly different case structures, i.e. a table might be created
'MYTABLE', and accessed 'mytable', 'MyTable' or 'MYTABLE' depending on
the context. Is there anyway to disable case-sensitivity in this case,
either through Postgres config, or the ODBC drivers? I have no access to
re-configure the clients, due to their wonderfully closed sourcedness.
Thanks
Dana Powers
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: [GENERAL] Case sensitivity
1999-09-20 03:43 Case sensitivity Dana Powers <powersdp@ctrvax.Vanderbilt.Edu>
@ 1999-09-20 04:05 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-09-20 15:58 ` Re: [GENERAL] Case sensitivity Dana Powers <dana@fphome.com>
0 siblings, 1 reply; 80+ messages in thread
From: Bruce Momjian @ 1999-09-20 04:05 UTC (permalink / raw)
To: Dana Powers <powersdp@ctrvax.Vanderbilt.Edu>; +Cc: pgsql-general@hub.org
> I am evaluating whether it is possible to migrate our Contact manager
> system to Postgres on Linux, but I am having difficulty because the
> client software was most moronically programmed to access tables using
> wildly different case structures, i.e. a table might be created
> 'MYTABLE', and accessed 'mytable', 'MyTable' or 'MYTABLE' depending on
> the context. Is there anyway to disable case-sensitivity in this case,
> either through Postgres config, or the ODBC drivers? I have no access to
We have no trouble accessing tables with different case. TABLE, Table,
table and taBLe are all the same to us, unless you put the table name in
double-quotes.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: [GENERAL] Case sensitivity
1999-09-20 03:43 Case sensitivity Dana Powers <powersdp@ctrvax.Vanderbilt.Edu>
1999-09-20 04:05 ` Re: [GENERAL] Case sensitivity Bruce Momjian <maillist@candle.pha.pa.us>
@ 1999-09-20 15:58 ` Dana Powers <dana@fphome.com>
1999-09-20 16:32 ` Re: [GENERAL] Case sensitivity Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 1 reply; 80+ messages in thread
From: Dana Powers @ 1999-09-20 15:58 UTC (permalink / raw)
To: pgsql-general@hub.org
> > I am evaluating whether it is possible to migrate our Contact manager
> > system to Postgres on Linux, but I am having difficulty because the
> > client software was most moronically programmed to access tables using
> > wildly different case structures, i.e. a table might be created
> > 'MYTABLE', and accessed 'mytable', 'MyTable' or 'MYTABLE' depending on
> > the context. Is there anyway to disable case-sensitivity in this case,
> > either through Postgres config, or the ODBC drivers? I have no access to
>
> We have no trouble accessing tables with different case. TABLE, Table,
> table and taBLe are all the same to us, unless you put the table name in
> double-quotes.
The problem is, if you create a table without "", the table name
defaults to lower case, and all non ""ed references to the table also
default to all lower case but if you create a table with ""s you cannot
access it unless you specify all references to it also in ""s. My
question is, is there anyway to nullify the "" behavior and set all
tables to lowercase?
Dana Powers
powersdp@ctrvax.vanderbilt.edu
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: [GENERAL] Case sensitivity
1999-09-20 03:43 Case sensitivity Dana Powers <powersdp@ctrvax.Vanderbilt.Edu>
1999-09-20 04:05 ` Re: [GENERAL] Case sensitivity Bruce Momjian <maillist@candle.pha.pa.us>
1999-09-20 15:58 ` Re: [GENERAL] Case sensitivity Dana Powers <dana@fphome.com>
@ 1999-09-20 16:32 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-09-20 17:04 ` Re: [GENERAL] Case sensitivity Dana Powers <dana@fphome.com>
1999-09-21 04:43 ` Re: [GENERAL] Case sensitivity Charles Tassell <ctassell@isn.net>
0 siblings, 2 replies; 80+ messages in thread
From: Bruce Momjian @ 1999-09-20 16:32 UTC (permalink / raw)
To: Dana Powers <dana@fphome.com>; +Cc: pgsql-general@hub.org
> > > I am evaluating whether it is possible to migrate our Contact manager
> > > system to Postgres on Linux, but I am having difficulty because the
> > > client software was most moronically programmed to access tables using
> > > wildly different case structures, i.e. a table might be created
> > > 'MYTABLE', and accessed 'mytable', 'MyTable' or 'MYTABLE' depending on
> > > the context. Is there anyway to disable case-sensitivity in this case,
> > > either through Postgres config, or the ODBC drivers? I have no access to
> >
> > We have no trouble accessing tables with different case. TABLE, Table,
> > table and taBLe are all the same to us, unless you put the table name in
> > double-quotes.
>
> The problem is, if you create a table without "", the table name
> defaults to lower case, and all non ""ed references to the table also
> default to all lower case but if you create a table with ""s you cannot
> access it unless you specify all references to it also in ""s. My
> question is, is there anyway to nullify the "" behavior and set all
> tables to lowercase?
So you are creating with quotes, but want to access it without quotes?
Can't do that. Why not just remove the quotes from the queries? I
suppose you could hack out the quote stuff to force everything to
lowercase, even if it has quotes.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: [GENERAL] Case sensitivity
1999-09-20 03:43 Case sensitivity Dana Powers <powersdp@ctrvax.Vanderbilt.Edu>
1999-09-20 04:05 ` Re: [GENERAL] Case sensitivity Bruce Momjian <maillist@candle.pha.pa.us>
1999-09-20 15:58 ` Re: [GENERAL] Case sensitivity Dana Powers <dana@fphome.com>
1999-09-20 16:32 ` Re: [GENERAL] Case sensitivity Bruce Momjian <maillist@candle.pha.pa.us>
@ 1999-09-20 17:04 ` Dana Powers <dana@fphome.com>
1 sibling, 0 replies; 80+ messages in thread
From: Dana Powers @ 1999-09-20 17:04 UTC (permalink / raw)
To: pgsql-general@hub.org
Bruce Momjian wrote:
>
> > > > I am evaluating whether it is possible to migrate our Contact manager
> > > > system to Postgres on Linux, but I am having difficulty because the
> > > > client software was most moronically programmed to access tables using
> > The problem is, if you create a table without "", the table name
> > defaults to lower case, and all non ""ed references to the table also
> > default to all lower case but if you create a table with ""s you cannot
> > access it unless you specify all references to it also in ""s. My
> > question is, is there anyway to nullify the "" behavior and set all
> > tables to lowercase?
>
> So you are creating with quotes, but want to access it without quotes?
> Can't do that. Why not just remove the quotes from the queries? I
> suppose you could hack out the quote stuff to force everything to
> lowercase, even if it has quotes.
>
Problem is, I don't have control over the client queries. Third party
closed source contact manager. So I guess I'll try to hack something
together. Thanks Bruce
Dana
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: [GENERAL] Case sensitivity
1999-09-20 03:43 Case sensitivity Dana Powers <powersdp@ctrvax.Vanderbilt.Edu>
1999-09-20 04:05 ` Re: [GENERAL] Case sensitivity Bruce Momjian <maillist@candle.pha.pa.us>
1999-09-20 15:58 ` Re: [GENERAL] Case sensitivity Dana Powers <dana@fphome.com>
1999-09-20 16:32 ` Re: [GENERAL] Case sensitivity Bruce Momjian <maillist@candle.pha.pa.us>
@ 1999-09-21 04:43 ` Charles Tassell <ctassell@isn.net>
1 sibling, 0 replies; 80+ messages in thread
From: Charles Tassell @ 1999-09-21 04:43 UTC (permalink / raw)
To: pgsql-general@hub.org
Just a suggestion, but if you have or are a C programmer, you could edit
the part of Postgres that receives queries to convert everything it
receives to lower case. Might slow things down a bit, but it should solve
your problem. Alternatively, you could do the same thing to the ODBC
driver, which might be a bit easier and have less of a performance impact.
At 02:04 PM 9/20/99, Dana Powers wrote:
>Bruce Momjian wrote:
>>
>> > > > I am evaluating whether it is possible to migrate our Contact manager
>> > > > system to Postgres on Linux, but I am having difficulty because the
>> > > > client software was most moronically programmed to access tables using
>> > The problem is, if you create a table without "", the table name
>> > defaults to lower case, and all non ""ed references to the table also
>> > default to all lower case but if you create a table with ""s you cannot
>> > access it unless you specify all references to it also in ""s. My
>> > question is, is there anyway to nullify the "" behavior and set all
>> > tables to lowercase?
>>
>> So you are creating with quotes, but want to access it without quotes?
>> Can't do that. Why not just remove the quotes from the queries? I
>> suppose you could hack out the quote stuff to force everything to
>> lowercase, even if it has quotes.
>>
>Problem is, I don't have control over the client queries. Third party
>closed source contact manager. So I guess I'll try to hack something
>together. Thanks Bruce
>Dana
>
>************
>
^ permalink raw reply [nested|flat] 80+ messages in thread
* Case sensitivity
@ 2001-02-27 05:47 Bruce Richardson <brichard@cafod.org.uk>
2001-02-27 15:07 ` Re: Case sensitivity Oliver Elphick <olly@lfix.co.uk>
2001-02-27 16:08 ` Re: Case sensitivity Joel Burton <jburton@scw.org>
0 siblings, 2 replies; 80+ messages in thread
From: brichard@cafod.org.uk @ 2001-02-27 05:47 UTC (permalink / raw)
To: pgsql-general
Discovering that text fields in Postgres are case sensitive - so "Text"
<> "text" and both can go into the same UNIQUE column - threw me a bit,
as this is different from other databases I've worked with.
Is there a toggle to turn off case sensitivity, either for a whole
database or for a column? Or do I just have to get used to ~*?
--
Bruce
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2001-02-27 05:47 Case sensitivity Bruce Richardson <brichard@cafod.org.uk>
@ 2001-02-27 15:07 ` Oliver Elphick <olly@lfix.co.uk>
1 sibling, 0 replies; 80+ messages in thread
From: Oliver Elphick @ 2001-02-27 15:07 UTC (permalink / raw)
To: brichard@cafod.org.uk; +Cc: pgsql-general
Bruce Richardson wrote:
>Discovering that text fields in Postgres are case sensitive - so "Text"
><> "text" and both can go into the same UNIQUE column - threw me a bit,
>as this is different from other databases I've worked with.
Well, they are different, aren't they?
>Is there a toggle to turn off case sensitivity, either for a whole
>database or for a column? Or do I just have to get used to ~*?
You can create a unique index on lower(column); ~* is very nice,
any way.
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"If we confess our sins, he is faithful and just to
forgive us our sins, and to cleanse us from all
unrighteousness." I John 1:9
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2001-02-27 05:47 Case sensitivity Bruce Richardson <brichard@cafod.org.uk>
@ 2001-02-27 16:08 ` Joel Burton <jburton@scw.org>
1 sibling, 0 replies; 80+ messages in thread
From: Joel Burton @ 2001-02-27 16:08 UTC (permalink / raw)
To: Bruce Richardson <brichard@cafod.org.uk>; +Cc: pgsql-general
On Tue, 27 Feb 2001, Bruce Richardson wrote:
> Discovering that text fields in Postgres are case sensitive - so "Text"
> <> "text" and both can go into the same UNIQUE column - threw me a bit,
> as this is different from other databases I've worked with.
>
> Is there a toggle to turn off case sensitivity, either for a whole
> database or for a column? Or do I just have to get used to ~*?
No, you can't turn it off, and yes, many people do notice that it's not
the way some other databases work. (Some database make this configurable).
You can use ~*, you can also index on function results (eg, you can create
an index on a function. So, you could
create unique index yourtable_pkey on yourtable ( upper(pkeyfield));
which would only allow one 'Joel', regardless of how you cap me.
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington
^ permalink raw reply [nested|flat] 80+ messages in thread
* case sensitivity
@ 2001-05-12 10:23 Simon Crute <simon-news@nospam.geordie.demon.co.uk>
2001-05-13 03:09 ` Re: case sensitivity Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 80+ messages in thread
From: Simon Crute @ 2001-05-12 10:23 UTC (permalink / raw)
To: pgsql-general
Hi all,
I'm having some problems converting a CGI app from oracle to postgres (it
uses Perl & DBI)
When using sth->fetchrow_hashref() against the Oracle DBD, the keys are all
in upper case.
When using sth->fetchrow_hashref() against the Postgres DBD, the keys are
all in lower case.
Now, I'm not sure if oracle converts all column names to upper case, or if
postgres doesn't and "should", but I need to know if there's an easy way to
persuade the postgres DBD driver to return the names in upper case.
As separate point, postgres seems to be bothered about case a lot more than
oracle. My DBA spent ages looking at why a query using count didn't work.
Took a non DBA like me to suggest trying upper case :)
Is there anyway of "relaxing" postgres's demands to exact case, or is this
by design ?
Thanks.
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: case sensitivity
2001-05-12 10:23 case sensitivity Simon Crute <simon-news@nospam.geordie.demon.co.uk>
@ 2001-05-13 03:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-05-13 10:21 ` Re: case sensitivity Simon Crute <simon-news@nospam.geordie.demon.co.uk>
0 siblings, 1 reply; 80+ messages in thread
From: Tom Lane @ 2001-05-13 03:09 UTC (permalink / raw)
To: Simon Crute <simon-news@nospam.geordie.demon.co.uk>; +Cc: pgsql-general
"Simon Crute" <simon-news@nospam.geordie.demon.co.uk> writes:
> As separate point, postgres seems to be bothered about case a lot more than
> oracle.
AFAIK we're exactly as picky about it as Oracle, just in the opposite
direction.
regards, tom lane
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: case sensitivity
2001-05-12 10:23 case sensitivity Simon Crute <simon-news@nospam.geordie.demon.co.uk>
2001-05-13 03:09 ` Re: case sensitivity Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-05-13 10:21 ` Simon Crute <simon-news@nospam.geordie.demon.co.uk>
2001-05-14 21:14 ` Re: Re: case sensitivity newsreader@mediaone.net
2001-05-14 22:49 ` Re: Re: case sensitivity Gilles DAROLD <gilles@darold.net>
0 siblings, 2 replies; 80+ messages in thread
From: Simon Crute @ 2001-05-13 10:21 UTC (permalink / raw)
To: pgsql-general
If anyone's interested,
I found the answer to my first question buried in the DBI docs
(fetch_hashref("NAME_uc") if anyone's interested)
With regards to case
> AFAIK we're exactly as picky about it as Oracle, just in the opposite
> direction.
I think there's some differences in function names. In oracle I think you
can get away with count(), or COUNT(), in postgres it has to be COUNT(), but
this was probably going through the perl DBI, so that may have been
complicating things.
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Re: case sensitivity
2001-05-12 10:23 case sensitivity Simon Crute <simon-news@nospam.geordie.demon.co.uk>
2001-05-13 03:09 ` Re: case sensitivity Tom Lane <tgl@sss.pgh.pa.us>
2001-05-13 10:21 ` Re: case sensitivity Simon Crute <simon-news@nospam.geordie.demon.co.uk>
@ 2001-05-14 21:14 ` newsreader@mediaone.net
1 sibling, 0 replies; 80+ messages in thread
From: newsreader@mediaone.net @ 2001-05-14 21:14 UTC (permalink / raw)
To: Simon Crute <simon-news@nospam.geordie.demon.co.uk>; +Cc: pgsql-general
On Sun, May 13, 2001 at 11:21:20AM +0100, Simon Crute wrote:
> If anyone's interested,
> can get away with count(), or COUNT(), in postgres it has to be COUNT(), but
> this was probably going through the perl DBI, so that may have been
been using count() since the
beginning of time
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Re: case sensitivity
2001-05-12 10:23 case sensitivity Simon Crute <simon-news@nospam.geordie.demon.co.uk>
2001-05-13 03:09 ` Re: case sensitivity Tom Lane <tgl@sss.pgh.pa.us>
2001-05-13 10:21 ` Re: case sensitivity Simon Crute <simon-news@nospam.geordie.demon.co.uk>
@ 2001-05-14 22:49 ` Gilles DAROLD <gilles@darold.net>
1 sibling, 0 replies; 80+ messages in thread
From: Gilles DAROLD @ 2001-05-14 22:49 UTC (permalink / raw)
To: Simon Crute <simon-news@nospam.geordie.demon.co.uk>; +Cc: pgsql-general
Simon Crute wrote:
> If anyone's interested,
> I found the answer to my first question buried in the DBI docs
> (fetch_hashref("NAME_uc") if anyone's interested)
>
> With regards to case
> > AFAIK we're exactly as picky about it as Oracle, just in the opposite
> > direction.
>
> I think there's some differences in function names. In oracle I think you
> can get away with count(), or COUNT(), in postgres it has to be COUNT(), but
> this was probably going through the perl DBI, so that may have been
> complicating things.
Oracle doesn't care about case sensitive in queries, Postgres too if you don't
quote tablename or columnname at creation time. If you use quote and upercase
at creation time you may quote it as well in you're queries. Function can be
lower
or upper case it doesn't matter at least in PostgreSQL 7.0.3.
SELECT COUNT(*) FROM "T_USERPREF";
select count(*) from "T_USERPREF";
Works well.
But you want columname returned as uppercase and you find the way...
^ permalink raw reply [nested|flat] 80+ messages in thread
* Case sensitivity
@ 2001-08-14 06:09 Scott Gilbert <scott.gilbert@netregistry.au.com>
2001-08-14 06:47 ` Re: Case sensitivity John Clark Naldoza y Lopez <njclark@ntsp.nec.co.jp>
0 siblings, 1 reply; 80+ messages in thread
From: Scott Gilbert @ 2001-08-14 06:09 UTC (permalink / raw)
To: pgsql-general
We intend to move from M$ Access to PostgreSQL, however, much of our legacy
code operates under the assumption that string or text comparisons are case
insensitive whereas postgres is case sensitive. This issue extends to SQL
Select statements as well as straight comparison (i.e. in addition to the <,
> and = operators, the WHERE and IN clauses in SQL statements are also
issues) so we can't simply modify the function attached to the comparison
operators. Is there some means of modifying the behaviour of PostgreSQL so
that it is case insensitive?
Attachments:
[application/ms-tnef] winmail.dat (1.7K, ../../NFBBLLGHLDHKCLMNHNBEIEOCCEAA.scott.gilbert@netregistry.au.com/2-winmail.dat)
download
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2001-08-14 06:09 Case sensitivity Scott Gilbert <scott.gilbert@netregistry.au.com>
@ 2001-08-14 06:47 ` John Clark Naldoza y Lopez <njclark@ntsp.nec.co.jp>
2001-08-14 04:25 ` Re: Case sensitivity Digital Wokan <wokan@home.com>
0 siblings, 1 reply; 80+ messages in thread
From: John Clark Naldoza y Lopez @ 2001-08-14 06:47 UTC (permalink / raw)
To: scott.gilbert@netregistry.au.com; +Cc: pgsql-general
Hello Scott,
PostgreSQL also supports regular expressions, _VERY_ useful..;-)
~* regular expression, case-insensitive..;-)
Try to get a copy of Bruce's book
Introduction and Concepts
by Bruce Momjian
A sample from the book:
SELECT * FROM friend WHERE firstname ~* '[bc]'
read it online, or just read the docs online...;-)
http://techdocs.postgresql.org
Cheers,
John Clark
p.s. don't change PostgreSQL, change your SQL statements...;-)
Scott Gilbert wrote:
>
> We intend to move from M$ Access to PostgreSQL, however, much of our legacy
> code operates under the assumption that string or text comparisons are case
> insensitive whereas postgres is case sensitive. This issue extends to SQL
> Select statements as well as straight comparison (i.e. in addition to the <,
> > and = operators, the WHERE and IN clauses in SQL statements are also
> issues) so we can't simply modify the function attached to the comparison
> operators. Is there some means of modifying the behaviour of PostgreSQL so
> that it is case insensitive?
--
/) John Clark Naldoza y Lopez (\
/ ) Software Design Engineer III ( \
_( (_ _ Web-Application Development _) )_
(((\ \> /_> Cable Modem Network Management System <_\ </ /)))
(\\\\ \_/ / NEC Telecom Software Phils., Inc. \ \_/ ////)
\ / \ /
\ _/ phone: (+63 32) 233-9142 loc. 3113 \_ /
/ / cellphone: (+63 919) 399-4742 \ \
/ / email: njclark@ntsp.nec.co.jp \ \
"Intelligence is the ability to avoid doing work, yet getting the work
done"
--Linus Torvalds
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2001-08-14 06:09 Case sensitivity Scott Gilbert <scott.gilbert@netregistry.au.com>
2001-08-14 06:47 ` Re: Case sensitivity John Clark Naldoza y Lopez <njclark@ntsp.nec.co.jp>
@ 2001-08-14 04:25 ` Digital Wokan <wokan@home.com>
0 siblings, 0 replies; 80+ messages in thread
From: Digital Wokan @ 2001-08-14 04:25 UTC (permalink / raw)
To: pgsql-general
Very good book. I have the .pdf file of it from before they took it off
the site.
John Clark Naldoza y Lopez wrote:
>
> Hello Scott,
>
> PostgreSQL also supports regular expressions, _VERY_ useful..;-)
>
> ~* regular expression, case-insensitive..;-)
>
> Try to get a copy of Bruce's book
>
> Introduction and Concepts
> by Bruce Momjian
>
> A sample from the book:
> SELECT * FROM friend WHERE firstname ~* '[bc]'
>
> read it online, or just read the docs online...;-)
>
> http://techdocs.postgresql.org
>
> Cheers,
>
> John Clark
>
> p.s. don't change PostgreSQL, change your SQL statements...;-)
>
> Scott Gilbert wrote:
> >
> > We intend to move from M$ Access to PostgreSQL, however, much of our legacy
> > code operates under the assumption that string or text comparisons are case
> > insensitive whereas postgres is case sensitive. This issue extends to SQL
> > Select statements as well as straight comparison (i.e. in addition to the <,
> > > and = operators, the WHERE and IN clauses in SQL statements are also
> > issues) so we can't simply modify the function attached to the comparison
> > operators. Is there some means of modifying the behaviour of PostgreSQL so
> > that it is case insensitive?
>
> --
> /) John Clark Naldoza y Lopez (\
> / ) Software Design Engineer III ( \
> _( (_ _ Web-Application Development _) )_
> (((\ \> /_> Cable Modem Network Management System <_\ </ /)))
> (\\\\ \_/ / NEC Telecom Software Phils., Inc. \ \_/ ////)
> \ / \ /
> \ _/ phone: (+63 32) 233-9142 loc. 3113 \_ /
> / / cellphone: (+63 919) 399-4742 \ \
> / / email: njclark@ntsp.nec.co.jp \ \
>
> "Intelligence is the ability to avoid doing work, yet getting the work
> done"
> --Linus Torvalds
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 80+ messages in thread
* case sensitivity?
@ 2001-08-26 21:06 Robert J. Sanford, Jr. <rsanford@nolimitsystems.com>
2001-08-27 03:35 ` Re: case sensitivity? hubert depesz lubaczewski <depesz@depesz.pl>
0 siblings, 1 reply; 80+ messages in thread
From: Robert J. Sanford, Jr. @ 2001-08-26 21:06 UTC (permalink / raw)
To: pgsql-general
setup - pgsql 7.1.2 cygwin binary distribution
i first noticed this when running psql - whenever i
perform a select all of the columns are returned as
all lowercase. that wasn't too disturbing, i just
found it to be slightly odd.
what was disturbing was when the same thing happened
going through the odbc driver on win32 and all of my
old perl code that worked under microsoft stopped
working under postgres because i was looking for
keys in the hash retrieved by the $dsn->dataHash()
by the MixedCase names that i used to create the
tables with in the sql.
that meant that i had to run through my code to
modify all of the hash references to all lowercase.
needless to say, that was a pain.
is there any setting that will allow postgres to
keep mixed case column names when creating a db?
rjsjr
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: case sensitivity?
2001-08-26 21:06 case sensitivity? Robert J. Sanford, Jr. <rsanford@nolimitsystems.com>
@ 2001-08-27 03:35 ` hubert depesz lubaczewski <depesz@depesz.pl>
0 siblings, 0 replies; 80+ messages in thread
From: hubert depesz lubaczewski @ 2001-08-27 03:35 UTC (permalink / raw)
To: Robert J. Sanford, Jr. <rsanford@nolimitsystems.com>; +Cc: pgsql-general
On Sun, 26 Aug 2001 16:06:14 -0500
"Robert J. Sanford, Jr." <rsanford@nolimitsystems.com> wrote:
> is there any setting that will allow postgres to
> keep mixed case column names when creating a db?
yes:
when you create table make sure all identifiers are inside quotation marks
("). like this:
create table "MyTable" (
id SERIAL,
"Name" TEXT,
"Phone" TEXT,
PRIMARY KEY (id)
);
but since then all your select's have to work with quotation mark, i.e. this
will not work:
select name from mytable;
nor will work this:
select Name from MyTable;
you have to explicity do:
select "Name" from "MyTable";
the reason is very simple. pgsql internally converts all names, identifiers
and so on to lower case. both in create statements and in select's, update's
and so on.
i hope you have it clear now.
depesz
p.s. sorry for my english
--
hubert depesz lubaczewski http://www.depesz.pl/
------------------------------------------------------------------------
... and the end of all our exploring will be to arrive where we started
and know the place for the first time. -- T. S. Eliot
^ permalink raw reply [nested|flat] 80+ messages in thread
* Case sensitivity
@ 2002-06-20 14:20 Varun Kacholia <varunk@cse.iitb.ac.in>
2002-06-20 15:13 ` Re: Case sensitivity Andrew Sullivan <andrew@libertyrms.info>
2002-06-20 15:16 ` Re: Case sensitivity Darren Ferguson <darren@crystalballinc.com>
0 siblings, 2 replies; 80+ messages in thread
From: Varun Kacholia @ 2002-06-20 14:20 UTC (permalink / raw)
To: pgsql-general
Hi,
Well while moving from Mysql to POstgresql this was one more difference
: Case Sensitivity.
The problem is in this query
select * from table where name='Word'
select * from table where name='word'
Mysql treated both as same but postgres does not..i appreciate that
it is good but is there a way that 'Word' can be matched with 'word'?
i mean my queries can specify whether to be case sensitive or not.
is there any such provision in postgres?
or is it always case sensitive?
thanks
--
------
Varun
It is easier to write an incorrect program than understand a correct one
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2002-06-20 14:20 Case sensitivity Varun Kacholia <varunk@cse.iitb.ac.in>
@ 2002-06-20 15:13 ` Andrew Sullivan <andrew@libertyrms.info>
1 sibling, 0 replies; 80+ messages in thread
From: Andrew Sullivan @ 2002-06-20 15:13 UTC (permalink / raw)
To: pgsql-general
On Thu, Jun 20, 2002 at 07:53:06PM +0530, Varun Kacholia wrote:
> Hi,
> Well while moving from Mysql to POstgresql this was one more difference
> : Case Sensitivity.
> The problem is in this query
> select * from table where name='Word'
> select * from table where name='word'
>
> Mysql treated both as same
Really!? Wow. Is there a way to differentiate 'Word' from 'word',
then? (I mean, LeBlanc and Leblanc are not the same, when you're
addressing people. And if you think the difference isn't important,
you've never been to Acadia!)
> it is good but is there a way that 'Word' can be matched with 'word'?
Sure. Use ILIKE:
SELECT * FROM table WHERE name ILIKE 'word';
A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2002-06-20 14:20 Case sensitivity Varun Kacholia <varunk@cse.iitb.ac.in>
@ 2002-06-20 15:16 ` Darren Ferguson <darren@crystalballinc.com>
1 sibling, 0 replies; 80+ messages in thread
From: Darren Ferguson @ 2002-06-20 15:16 UTC (permalink / raw)
To: Varun Kacholia <varunk@cse.iitb.ac.in>; +Cc: pgsql-general
Ok the way around this is
SELECT * FROM table WHERE LOWER(name) = LOWER('Word');
SEELCT * FROM table WHERE UPPER(name) = UPPER('Word');
Note that if you have indexes on these fields then they will not work when
upper and lower are used.
To use an index create an index on upper or lower name depending upon what
you want to use
HTH
Darren Ferguson
On Thu, 20 Jun 2002, Varun Kacholia wrote:
> Hi,
> Well while moving from Mysql to POstgresql this was one more difference
> : Case Sensitivity.
> The problem is in this query
> select * from table where name='Word'
> select * from table where name='word'
>
> Mysql treated both as same but postgres does not..i appreciate that
> it is good but is there a way that 'Word' can be matched with 'word'?
> i mean my queries can specify whether to be case sensitive or not.
> is there any such provision in postgres?
> or is it always case sensitive?
> thanks
>
--
Darren Ferguson
^ permalink raw reply [nested|flat] 80+ messages in thread
* Case sensitivity
@ 2004-01-11 09:29 Dario Ottaviano <dario_ottaviano@togliquestovirgilio.it>
2004-01-12 09:57 ` Re: Case sensitivity Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 80+ messages in thread
From: Dario Ottaviano @ 2004-01-11 09:29 UTC (permalink / raw)
To: pgsql-general
I use postgres on a window server (IIS 5.1)
Is there anybody that knows if is possible to make postgres no case
sensitive in the manipulating data into tables/views?
Thank you.
D.Ottaviano
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2004-01-11 09:29 Case sensitivity Dario Ottaviano <dario_ottaviano@togliquestovirgilio.it>
@ 2004-01-12 09:57 ` Richard Huxton <dev@archonet.com>
2004-01-12 17:58 ` Re: Case sensitivity scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 1 reply; 80+ messages in thread
From: Richard Huxton @ 2004-01-12 09:57 UTC (permalink / raw)
To: Dario Ottaviano <dario_ottaviano@togliquestovirgilio.it>; pgsql-general
On Sunday 11 January 2004 09:29, Dario Ottaviano wrote:
> I use postgres on a window server (IIS 5.1)
> Is there anybody that knows if is possible to make postgres no case
> sensitive in the manipulating data into tables/views?
There's no general "case_sensitive = yes/no" flag. There are case-insensitive
operators you can use though.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2004-01-11 09:29 Case sensitivity Dario Ottaviano <dario_ottaviano@togliquestovirgilio.it>
2004-01-12 09:57 ` Re: Case sensitivity Richard Huxton <dev@archonet.com>
@ 2004-01-12 17:58 ` scott.marlowe <scott.marlowe@ihs.com>
2004-01-12 22:22 ` Re: Case sensitivity Richard Huxton <dev@archonet.com>
2004-01-12 22:27 ` Re: Case sensitivity Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 80+ messages in thread
From: scott.marlowe @ 2004-01-12 17:58 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Dario Ottaviano <dario_ottaviano@togliquestovirgilio.it>; pgsql-general
On Mon, 12 Jan 2004, Richard Huxton wrote:
> On Sunday 11 January 2004 09:29, Dario Ottaviano wrote:
> > I use postgres on a window server (IIS 5.1)
> > Is there anybody that knows if is possible to make postgres no case
> > sensitive in the manipulating data into tables/views?
>
> There's no general "case_sensitive = yes/no" flag. There are case-insensitive
> operators you can use though.
Is there a case insensitive locale? Or is that even possible?
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2004-01-11 09:29 Case sensitivity Dario Ottaviano <dario_ottaviano@togliquestovirgilio.it>
2004-01-12 09:57 ` Re: Case sensitivity Richard Huxton <dev@archonet.com>
2004-01-12 17:58 ` Re: Case sensitivity scott.marlowe <scott.marlowe@ihs.com>
@ 2004-01-12 22:22 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 80+ messages in thread
From: Richard Huxton @ 2004-01-12 22:22 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Dario Ottaviano <dario_ottaviano@togliquestovirgilio.it>; pgsql-general
On Monday 12 January 2004 17:58, scott.marlowe wrote:
> On Mon, 12 Jan 2004, Richard Huxton wrote:
> > On Sunday 11 January 2004 09:29, Dario Ottaviano wrote:
> > > I use postgres on a window server (IIS 5.1)
> > > Is there anybody that knows if is possible to make postgres no case
> > > sensitive in the manipulating data into tables/views?
> >
> > There's no general "case_sensitive = yes/no" flag. There are
> > case-insensitive operators you can use though.
>
> Is there a case insensitive locale? Or is that even possible?
I've heard it proposed, but don't believe anyone liked the idea enough to do
it. With my limited understanding of encoding/locale issues, that'd mean
adding support at the OS level, I think.
The other option would be an IVARCHAR type with suitably defined operators,
but that's hardly very portable.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2004-01-11 09:29 Case sensitivity Dario Ottaviano <dario_ottaviano@togliquestovirgilio.it>
2004-01-12 09:57 ` Re: Case sensitivity Richard Huxton <dev@archonet.com>
2004-01-12 17:58 ` Re: Case sensitivity scott.marlowe <scott.marlowe@ihs.com>
@ 2004-01-12 22:27 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 80+ messages in thread
From: Tom Lane @ 2004-01-12 22:27 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Richard Huxton <dev@archonet.com>; Dario Ottaviano <dario_ottaviano@togliquestovirgilio.it>; pgsql-general
"scott.marlowe" <scott.marlowe@ihs.com> writes:
> Is there a case insensitive locale? Or is that even possible?
It's certainly possible, but I don't know of any standard locales that
behave like that.
One could also imagine developing a new set of comparison operators that
do case-insensitive comparison, and then making a special index opclass
for that. The locale-insensitive operators Peter built to optimize LIKE
indexing in 7.4 would be a good model.
regards, tom lane
^ permalink raw reply [nested|flat] 80+ messages in thread
* Case sensitivity
@ 2004-02-09 23:01 Jason Tesser <JTesser@nbbc.edu>
2004-02-10 06:02 ` Re: Case sensitivity David Garamond <lists@zara.6.isreserved.com>
0 siblings, 1 reply; 80+ messages in thread
From: Jason Tesser @ 2004-02-09 23:01 UTC (permalink / raw)
To: pgsql-general
I am converting data from Access into Postgres and ran into an issue
with case sensitivity. Can I write queries in Access that will be case
insensitive without rewriting the queries. So I would like to know if
this be handled in Postgres or even if someone knows in Access. Thank
you.
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2004-02-09 23:01 Case sensitivity Jason Tesser <JTesser@nbbc.edu>
@ 2004-02-10 06:02 ` David Garamond <lists@zara.6.isreserved.com>
2004-02-10 06:33 ` Re: Case sensitivity Klint Gore <kg@kgb.une.edu.au>
2004-02-10 16:04 ` Re: Case sensitivity Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 2 replies; 80+ messages in thread
From: David Garamond @ 2004-02-10 06:02 UTC (permalink / raw)
To: Jason Tesser <JTesser@nbbc.edu>; +Cc: pgsql-general
Jason Tesser wrote:
> I am converting data from Access into Postgres and ran into an issue
> with case sensitivity. Can I write queries in Access that will be case
> insensitive without rewriting the queries. So I would like to know if
> this be handled in Postgres or even if someone knows in Access. Thank
> you.
I believe no. You can index on LOWER(f) or UPPER(f) but that still
requires a query rewriting to WHERE LOWER(f) = .... You can also write
INSERT triggers to convert values to upper case, but that still requires
query rewriting if you're comparing with mixed-case values.
This has been discussed many times. See the archive for the various
threads on this.
To be safe, if you want your application to be portable, you should
always assume case-sensitivity. AFAIK, only Access, SQL Server, and
MySQL that are case-insensitive by default while others including
Postgres are case-sensitive. Any other popular DB that are
case-sensitive out there?
--
dave
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2004-02-09 23:01 Case sensitivity Jason Tesser <JTesser@nbbc.edu>
2004-02-10 06:02 ` Re: Case sensitivity David Garamond <lists@zara.6.isreserved.com>
@ 2004-02-10 06:33 ` Klint Gore <kg@kgb.une.edu.au>
1 sibling, 0 replies; 80+ messages in thread
From: Klint Gore @ 2004-02-10 06:33 UTC (permalink / raw)
To: pgsql-general
On Tue, 10 Feb 2004 13:02:55 +0700, David Garamond <lists@zara.6.isreserved.com> wrote:
> To be safe, if you want your application to be portable, you should
> always assume case-sensitivity. AFAIK, only Access, SQL Server, and
> MySQL that are case-insensitive by default while others including
> Postgres are case-sensitive. Any other popular DB that are
> case-sensitive out there?
Sybase SQL Anywhere is not case sensitive by default. It has a setting
to change it.
klint.
+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : kg@kgb.une.edu.au : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---------------------------------------+-----------------+
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2004-02-09 23:01 Case sensitivity Jason Tesser <JTesser@nbbc.edu>
2004-02-10 06:02 ` Re: Case sensitivity David Garamond <lists@zara.6.isreserved.com>
@ 2004-02-10 16:04 ` Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 0 replies; 80+ messages in thread
From: Andrew Sullivan @ 2004-02-10 16:04 UTC (permalink / raw)
To: pgsql-general
On Tue, Feb 10, 2004 at 01:02:55PM +0700, David Garamond wrote:
>
> I believe no. You can index on LOWER(f) or UPPER(f) but that still
> requires a query rewriting to WHERE LOWER(f) = .... You can also write
> INSERT triggers to convert values to upper case, but that still requires
> query rewriting if you're comparing with mixed-case values.
But you could do some tricks with rules (plus the indexes and
triggers you mentioned) in order to lower() or upper() the input, if
you happen to know where you're likely to have the problems.
It's certainly true, however, that from the point of view of keeping
your data clean, you want these steps handled before the data
actually gets into the database.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The plural of anecdote is not data.
--Roger Brinner
^ permalink raw reply [nested|flat] 80+ messages in thread
* Case sensitivity
@ 2005-06-10 12:22 Howard Cole <howard.cole@selestial.com>
2005-06-12 18:49 ` Re: Case sensitivity Tom Lane <tgl@sss.pgh.pa.us>
2005-06-13 08:32 ` Re: Case sensitivity Howard Cole <howardnews@selestial.com>
0 siblings, 2 replies; 80+ messages in thread
From: Howard Cole @ 2005-06-10 12:22 UTC (permalink / raw)
To: pgsql-general
Has case sensitivity changed between 8.0.1 and 8.0.3 or am I missing
some setting somewhere?
I try the following sql on two database servers and get different results:
create table test (val varchar(10));
insert into test values ('A');
insert into test values ('a');
select * from test where val < 'a';
Results on 8.0.1 linux server (UNICODE)
val
-----
(0 rows)
Results on 8.0.3 windows server (UNICODE)
val
-----
A
(1 row)
I am guessing that the 8.0.3 on windows is the correct version - but
what changes this and how can I set it?
regards
Howard Cole
www.selestial.com
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2005-06-10 12:22 Case sensitivity Howard Cole <howard.cole@selestial.com>
@ 2005-06-12 18:49 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 80+ messages in thread
From: Tom Lane @ 2005-06-12 18:49 UTC (permalink / raw)
To: Howard Cole <howard.cole@selestial.com>; +Cc: pgsql-general
Howard Cole <howard.cole@selestial.com> writes:
> Has case sensitivity changed between 8.0.1 and 8.0.3
Nope.
> Results on 8.0.3 windows server (UNICODE)
Unicode doesn't work at all well on Windows. Consider using a different
encoding. Also, you'd better check that you have matching locale and
encoding settings on both those installations ...
regards, tom lane
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2005-06-10 12:22 Case sensitivity Howard Cole <howard.cole@selestial.com>
@ 2005-06-13 08:32 ` Howard Cole <howardnews@selestial.com>
1 sibling, 0 replies; 80+ messages in thread
From: Howard Cole @ 2005-06-13 08:32 UTC (permalink / raw)
To: pgsql-general
I looked into the problem further - and could not repeat the problem on
my windows XP machine. The problem was isolated to a server which uses
the 64bit edition of W2K3 server. Otherwise the W2K3 64bit edition seems
to run PG ok.
Are there any plans for fixing the UNICODE encoding on Windows?
Regards,
Howard Cole
www.selestial.com
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
@ 2005-06-12 18:32 Magnus Hagander <mha@sollentuna.net>
0 siblings, 0 replies; 80+ messages in thread
From: Magnus Hagander @ 2005-06-12 18:32 UTC (permalink / raw)
To: Howard Cole <howard.cole@selestial.com>; pgsql-general
> create table test (val varchar(10));
> insert into test values ('A');
> insert into test values ('a');
> select * from test where val < 'a';
>
> Results on 8.0.1 linux server (UNICODE)
> val
> -----
> (0 rows)
>
> Results on 8.0.3 windows server (UNICODE) val
> -----
> A
> (1 row)
>
> I am guessing that the 8.0.3 on windows is the correct
> version - but what changes this and how can I set it?
UNICODE is not currently supported on win32. We hope to have this done
in 8.1, but in 8.0 bad things like this will happen. See
http://www.postgresql.org/docs/faqs.FAQ_windows.html#2.6.
//Magnus
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
@ 2005-06-13 08:52 Magnus Hagander <mha@sollentuna.net>
2005-06-13 09:34 ` Re: Case sensitivity Howard Cole <howardnews@selestial.com>
0 siblings, 1 reply; 80+ messages in thread
From: Magnus Hagander @ 2005-06-13 08:52 UTC (permalink / raw)
To: Howard Cole <howardnews@selestial.com>; pgsql-general; +Cc: Palle Girgensohn <girgen@pingpong.net>
> I looked into the problem further - and could not repeat the
> problem on my windows XP machine. The problem was isolated to
> a server which uses the 64bit edition of W2K3 server.
> Otherwise the W2K3 64bit edition seems to run PG ok.
>
> Are there any plans for fixing the UNICODE encoding on Windows?
Yes. There is a patch floating around to use ICU, which will fix this
(amongst the other things it does). The hope is that it'll be completed
in time for 8.1.
//Magnus
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2005-06-13 08:52 Re: Case sensitivity Magnus Hagander <mha@sollentuna.net>
@ 2005-06-13 09:34 ` Howard Cole <howardnews@selestial.com>
0 siblings, 0 replies; 80+ messages in thread
From: Howard Cole @ 2005-06-13 09:34 UTC (permalink / raw)
To: pgsql-general
Tom, Magnus.
Thanks for the great support.
Howard Cole
www.selestial.com
Magnus Hagander wrote:
>>Are there any plans for fixing the UNICODE encoding on Windows?
>>
>>
>
>Yes. There is a patch floating around to use ICU, which will fix this
>(amongst the other things it does). The hope is that it'll be completed
>in time for 8.1.
>
>//Magnus
>
>
^ permalink raw reply [nested|flat] 80+ messages in thread
* Case sensitivity
@ 2005-08-06 09:36 Frank Millman <frank@chagford.com>
2005-08-08 09:55 ` Re: Case sensitivity Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 80+ messages in thread
From: Frank Millman @ 2005-08-06 09:36 UTC (permalink / raw)
To: pgsql-general
Hi all
Is there an LC_COLLATE setting, or any other method, which allows all data
in a database to be treated in a case-insensitive manner?
I have two scenarios in mind. There are workarounds for both of them, but it
would be nice if they were not necessary.
1. In a UNIQUE column, I would like a value of 'a' to be rejected if there
is already a value of 'A'. Workaround - create a unique index on
LOWER(col_name).
2. I would like WHERE col_name = 'x' and WHERE col_name LIKE 'x%' to find
'X' and 'X1'. Workaround - WHERE LOWER(col_name) = 'x' and WHERE col_name
ILIKE 'x%'.
TIA
Frank Millman
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2005-08-06 09:36 Case sensitivity Frank Millman <frank@chagford.com>
@ 2005-08-08 09:55 ` Richard Huxton <dev@archonet.com>
2005-08-09 07:35 ` Re: Case sensitivity Frank Millman <frank@chagford.com>
2005-08-10 11:18 ` Re: Case sensitivity Frank Millman <frank@chagford.com>
2005-08-10 13:05 ` Re: Case sensitivity Frank Millman <frank@chagford.com>
0 siblings, 3 replies; 80+ messages in thread
From: Richard Huxton @ 2005-08-08 09:55 UTC (permalink / raw)
To: Frank Millman <frank@chagford.com>; +Cc: pgsql-general
Frank Millman wrote:
> Hi all
>
> Is there an LC_COLLATE setting, or any other method, which allows all data
> in a database to be treated in a case-insensitive manner?
>
> I have two scenarios in mind. There are workarounds for both of them, but it
> would be nice if they were not necessary.
>
> 1. In a UNIQUE column, I would like a value of 'a' to be rejected if there
> is already a value of 'A'. Workaround - create a unique index on
> LOWER(col_name).
>
> 2. I would like WHERE col_name = 'x' and WHERE col_name LIKE 'x%' to find
> 'X' and 'X1'. Workaround - WHERE LOWER(col_name) = 'x' and WHERE col_name
> ILIKE 'x%'.
You could define your own type if you want to go to that level of
effort. Or, you could just decide only to allow upper-case values (or
lower-case).
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2005-08-06 09:36 Case sensitivity Frank Millman <frank@chagford.com>
2005-08-08 09:55 ` Re: Case sensitivity Richard Huxton <dev@archonet.com>
@ 2005-08-09 07:35 ` Frank Millman <frank@chagford.com>
2005-08-09 09:57 ` Re: Case sensitivity Martijn van Oosterhout <kleptog@svana.org>
2005-08-09 11:38 ` Re: Case sensitivity Richard Huxton <dev@archonet.com>
2 siblings, 2 replies; 80+ messages in thread
From: Frank Millman @ 2005-08-09 07:35 UTC (permalink / raw)
To: pgsql-general
Frank Millman wrote:
> Hi all
>
> Is there an LC_COLLATE setting, or any other method, which allows all
> data in a database to be treated in a case-insensitive manner?
I was hoping to stimulate some discussion on this topic, but it seems I will
have to kick-start it myself and see if anyone responds.
My area of interest is general accounting/business systems, using a typical
Western character set. I would imagine that this is a common scenario, but
it is not universal, so read my comments in this context.
In the good old days, data entry was always done in upper case, by dedicated
data entry personnel. These days, it is typically done by a wide variety of
individuals, who carry out a wide range of tasks, most of which require
lower case (word processing, email) with the occasional use of the shift key
to enter a capital letter.
In this context, here are two undesirable effects.
1. The user tries to call up account 'A001', but they enter 'a001'. First
problem, the system does not find the account. Second problem, the system
allows them to create a new account with the code 'a001'. Now you have
'A001' and 'a001'. This is a recipe for chaos.
2. The user tries to call up a product item using a search string on the
description. Assume they enter 'Wheel nut'. Assume further that the person
who created the product item used the description 'Wheel Nut'. Try
explaining to the user why the system cannot find the item they are looking
for.
I mentioned in my original post that there are workarounds for these
problems. However, it seems to me that in a typical system you would want to
apply the workaround on every table, and therefore there is a case for
saying that the database should handle it.
I have some experience of two other database systems, and it is of interest
to see how they handle it.
1. D3 (the latest implementation of the old Pick Database System). In the
early days it was case sensitive. When they brought out a new version in the
early 90's they changed it to case insensitive. As you would expect, an
upgrade required a full backup and restore. I was involved in many of these,
some of them quite large. On two occasions I found that accounts were out of
balance after the restore, and on investigation found that situations
similar to 'A001' 'a001' had crept into the old database, and on restore the
second insert was rejected as the first one already existed. When this was
explained to the user, the reaction was always concern that this 'error' had
been allowed to happen, and relief that the new version ensured that it
could never happen again.
2. MSSQL Server 2000. Each time you create a new database you have to
specify which 'collation' to use. There is a wide range available, both case
sensitive and case insensitive. The default (on my system at least) is case
insensitive, and I believe that in practice this is what most people want.
There may well be counter-arguments to this being handled by the database,
and I would be interested to hear them. However, from my point of view, if
this capability is not currently available in PostgreSQL, I would like to
propose that it is considered for some future release.
Looking forward to any comments.
Frank Millman
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2005-08-06 09:36 Case sensitivity Frank Millman <frank@chagford.com>
2005-08-08 09:55 ` Re: Case sensitivity Richard Huxton <dev@archonet.com>
2005-08-09 07:35 ` Re: Case sensitivity Frank Millman <frank@chagford.com>
@ 2005-08-09 09:57 ` Martijn van Oosterhout <kleptog@svana.org>
2005-08-09 11:04 ` Re: Case sensitivity Martijn van Oosterhout <kleptog@svana.org>
2005-08-09 15:02 ` Re: Case sensitivity Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 80+ messages in thread
From: Martijn van Oosterhout @ 2005-08-09 09:57 UTC (permalink / raw)
To: Frank Millman <frank@chagford.com>; +Cc: pgsql-general
On Tue, Aug 09, 2005 at 09:35:25AM +0200, Frank Millman wrote:
> Frank Millman wrote:
>
> > Hi all
> >
> > Is there an LC_COLLATE setting, or any other method, which allows all
> > data in a database to be treated in a case-insensitive manner?
>
> I was hoping to stimulate some discussion on this topic, but it seems I will
> have to kick-start it myself and see if anyone responds.
I know there have been complaints from people that their database is
sorting case-insensetively when they wish it wouldn't. This generally
happens when the LC_COLLATE is set to en_US or some such. However, I
think that even the en_US locale just fiddles the sort order, but
doesn't make upper and lowercase equal.
> I mentioned in my original post that there are workarounds for these
> problems. However, it seems to me that in a typical system you would want to
> apply the workaround on every table, and therefore there is a case for
> saying that the database should handle it.
These "workarounds" are the recommended way of dealing with this issue.
Another option would be to create a new datatype 'itext' which works
like text except it compares case insensetively. PostgreSQL is flexible
like that. Here's something to get you started, see below for example.
http://svana.org/kleptog/pgsql/type_itext.sql
At the moment it uses SQL functions for the comparisons, for production
you'd probably want to have them in C for performance. Also, it's not
pg_dump safe (no operator class support).
BTW, I can't beleive I'm the first to do this, but hey. It's also my
first type with index support so it may be buggy. But it does work for
basic tests...
Have a nice day,
--- snip ---
test=# create table itest ( pkey serial primary key, val itext );
NOTICE: CREATE TABLE will create implicit sequence "itest_pkey_seq"
for "serial" column "itest.pkey"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"itest_pkey" for table "itest"
CREATE TABLE
test=# insert into itest (val) values ('a');
INSERT 72279 1
test=# insert into itest (val) values ('A');
INSERT 72280 1
test=# insert into itest (val) values ('b');
INSERT 72281 1
test=# select * from itest where val = 'a';
pkey | val
------+-----
1 | a
2 | A
(2 rows)
test=# create unique index itest_val on itest(val);
ERROR: could not create unique index
DETAIL: Table contains duplicated values.
test=# delete from itest where val = 'a';
DELETE 2
test=# create unique index itest_val on itest(val);
CREATE INDEX
test=# insert into itest (val) values ('a');
INSERT 72284 1
test=# insert into itest (val) values ('A');
ERROR: duplicate key violates unique constraint "itest_val"
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2005-08-06 09:36 Case sensitivity Frank Millman <frank@chagford.com>
2005-08-08 09:55 ` Re: Case sensitivity Richard Huxton <dev@archonet.com>
2005-08-09 07:35 ` Re: Case sensitivity Frank Millman <frank@chagford.com>
2005-08-09 09:57 ` Re: Case sensitivity Martijn van Oosterhout <kleptog@svana.org>
@ 2005-08-09 11:04 ` Martijn van Oosterhout <kleptog@svana.org>
1 sibling, 0 replies; 80+ messages in thread
From: Martijn van Oosterhout @ 2005-08-09 11:04 UTC (permalink / raw)
To: Frank Millman <frank@chagford.com>; pgsql-general
On Tue, Aug 09, 2005 at 11:57:48AM +0200, Martijn van Oosterhout wrote:
> Another option would be to create a new datatype 'itext' which works
> like text except it compares case insensetively. PostgreSQL is flexible
> like that. Here's something to get you started, see below for example.
>
> http://svana.org/kleptog/pgsql/type_itext.sql
>
> At the moment it uses SQL functions for the comparisons, for production
> you'd probably want to have them in C for performance. Also, it's not
> pg_dump safe (no operator class support).
Oops, turns out there *is* a CREATE OPERATOR CLASS but my version of
psql doesn't have it in command completion. And when you use that it
*is* saved by pg_dump. Problem solved.
I've tested various things, DISTINCT works, ORDER BY works, GROUP BY
works. Neat huh?
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2005-08-06 09:36 Case sensitivity Frank Millman <frank@chagford.com>
2005-08-08 09:55 ` Re: Case sensitivity Richard Huxton <dev@archonet.com>
2005-08-09 07:35 ` Re: Case sensitivity Frank Millman <frank@chagford.com>
2005-08-09 09:57 ` Re: Case sensitivity Martijn van Oosterhout <kleptog@svana.org>
@ 2005-08-09 15:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-09 15:54 ` Re: Case sensitivity Martijn van Oosterhout <kleptog@svana.org>
1 sibling, 1 reply; 80+ messages in thread
From: Tom Lane @ 2005-08-09 15:02 UTC (permalink / raw)
To: Martijn van Oosterhout <kleptog@svana.org>; +Cc: Frank Millman <frank@chagford.com>; pgsql-general
Martijn van Oosterhout <kleptog@svana.org> writes:
> Another option would be to create a new datatype 'itext' which works
> like text except it compares case insensetively. PostgreSQL is flexible
> like that. Here's something to get you started, see below for example.
> http://svana.org/kleptog/pgsql/type_itext.sql
> At the moment it uses SQL functions for the comparisons, for production
> you'd probably want to have them in C for performance.
I believe there is a C-coded type like this on gborg ("citext" is the
name I think).
regards, tom lane
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2005-08-06 09:36 Case sensitivity Frank Millman <frank@chagford.com>
2005-08-08 09:55 ` Re: Case sensitivity Richard Huxton <dev@archonet.com>
2005-08-09 07:35 ` Re: Case sensitivity Frank Millman <frank@chagford.com>
2005-08-09 09:57 ` Re: Case sensitivity Martijn van Oosterhout <kleptog@svana.org>
2005-08-09 15:02 ` Re: Case sensitivity Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-08-09 15:54 ` Martijn van Oosterhout <kleptog@svana.org>
0 siblings, 0 replies; 80+ messages in thread
From: Martijn van Oosterhout @ 2005-08-09 15:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Frank Millman <frank@chagford.com>; pgsql-general
On Tue, Aug 09, 2005 at 11:02:47AM -0400, Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > Another option would be to create a new datatype 'itext' which works
> > like text except it compares case insensetively. PostgreSQL is flexible
> > like that. Here's something to get you started, see below for example.
>
> > http://svana.org/kleptog/pgsql/type_itext.sql
>
> > At the moment it uses SQL functions for the comparisons, for production
> > you'd probably want to have them in C for performance.
>
> I believe there is a C-coded type like this on gborg ("citext" is the
> name I think).
And so it is, full points to Tom. Here's the link:
http://gborg.postgresql.org/project/citext/projdisplay.php
I couldn't work out any obvious way to make google spit out this link
without the magic word "citext", so hopefully this reference will raise
the score enough that a plain google search for "case insensitive
postgresql" will find it.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2005-08-06 09:36 Case sensitivity Frank Millman <frank@chagford.com>
2005-08-08 09:55 ` Re: Case sensitivity Richard Huxton <dev@archonet.com>
2005-08-09 07:35 ` Re: Case sensitivity Frank Millman <frank@chagford.com>
@ 2005-08-09 11:38 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 80+ messages in thread
From: Richard Huxton @ 2005-08-09 11:38 UTC (permalink / raw)
To: Frank Millman <frank@chagford.com>; +Cc: pgsql-general
Frank Millman wrote:
> Frank Millman wrote:
>
>
>>Hi all
>>
>>Is there an LC_COLLATE setting, or any other method, which allows all
>>data in a database to be treated in a case-insensitive manner?
>
>
> I was hoping to stimulate some discussion on this topic, but it seems I will
> have to kick-start it myself and see if anyone responds.
>
> My area of interest is general accounting/business systems, using a typical
> Western character set. I would imagine that this is a common scenario, but
> it is not universal, so read my comments in this context.
>
> In the good old days, data entry was always done in upper case, by dedicated
> data entry personnel. These days, it is typically done by a wide variety of
> individuals, who carry out a wide range of tasks, most of which require
> lower case (word processing, email) with the occasional use of the shift key
> to enter a capital letter.
>
> In this context, here are two undesirable effects.
Martijn has pointed to a case-insensitive type, but I'll add a couple of
points.
> 1. The user tries to call up account 'A001', but they enter 'a001'. First
> problem, the system does not find the account. Second problem, the system
> allows them to create a new account with the code 'a001'. Now you have
> 'A001' and 'a001'. This is a recipe for chaos.
The basic problem here is that the value isn't text. This is partly the
fault of development-systems not having a way to deal with sophisticated
types in databases.
What should happen is that you define some suitable type "AccountCode"
which is defined as allowing character data in the form of (e.g.)
"[A-Z][0-9][0-9][0-9]". That type can cast to/from text but doesn't need
access to the full range of text-handling functions (e.g. concatenating
two account-codes is probably meaningless). Of course, you want to
define this in one place and have both the database constraints and
user-interface understand what you want.
Ironically, MS-Access does this quite well with its tight coupling of
user-interface and data storage.
> 2. The user tries to call up a product item using a search string on the
> description. Assume they enter 'Wheel nut'. Assume further that the person
> who created the product item used the description 'Wheel Nut'. Try
> explaining to the user why the system cannot find the item they are looking
> for.
Here, I'm not convinced a case-insensitive type is any more useful than
just using ILIKE.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2005-08-06 09:36 Case sensitivity Frank Millman <frank@chagford.com>
2005-08-08 09:55 ` Re: Case sensitivity Richard Huxton <dev@archonet.com>
@ 2005-08-10 11:18 ` Frank Millman <frank@chagford.com>
2005-08-10 13:53 ` Re: Case sensitivity John D. Burger <john@mitre.org>
2005-08-10 17:17 ` Re: Case sensitivity Roman Neuhauser <neuhauser@sigpipe.cz>
2 siblings, 2 replies; 80+ messages in thread
From: Frank Millman @ 2005-08-10 11:18 UTC (permalink / raw)
To: pgsql-general
Frank Millman wrote:
> Hi all
>
> Is there an LC_COLLATE setting, or any other method, which
allows all
> data in a database to be treated in a case-insensitive manner?
>
Thanks for all the replies, guys, I really appreciate it.
Here is what I have decided to do. If anyone sees any problems with my
approach, please let me know.
Interesting though the citypes are, I will not use them. If I get anywhere
with the app I am developing (it is making progress, but rather slowly) I
will release it as an open source project. I do not want to make it a
requirement that everyone must install a new datatype before they can use
it.
To handle searching for a row based on a string, I will use "LOWER(colname)
= 'x'" and "LOWER(colname) LIKE 'x%'". AFAICT, the second one is equivalent
to "colname ILIKE 'x%'", provided I force 'x' to lowercase first. I prefer
it as I need to support SQL Server as well, and this should work on both
platforms.
The problem of inserting 'a001' when 'A001' exists is still potentially
there, but it should not occur within my app, due to the way I handle table
maintenance. I do not distinguish between INSERT and UPDATE from the user's
point of view, but allow them to enter a primary key, do a SELECT, and then
assume UPDATE mode if the row exists, and INSERT mode if it does not. As I
will use SELECT WHERE LOWER(colname) = 'a001', it will find 'A001' and go
into UPDATE mode, so there should be no danger of duplication. It does not
feel entirely robust, so I will have to go through my app carefully to see
if I can find any loopholes in this theory.
Two questions.
1. Will SELECT WHERE LOWER(colname) = 'a001' use the index, or must I create
a separate index on LOWER(colname)?
2. I was expecting to have a problem with LOWER(colname) if the column was
of a numeric or date type, but it accepts it without complaining. Is it safe
for me to rely on this behaviour in the future?
Thanks again to everyone.
Frank
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2005-08-06 09:36 Case sensitivity Frank Millman <frank@chagford.com>
2005-08-08 09:55 ` Re: Case sensitivity Richard Huxton <dev@archonet.com>
2005-08-10 11:18 ` Re: Case sensitivity Frank Millman <frank@chagford.com>
@ 2005-08-10 13:53 ` John D. Burger <john@mitre.org>
1 sibling, 0 replies; 80+ messages in thread
From: John D. Burger @ 2005-08-10 13:53 UTC (permalink / raw)
To: Frank Millman <frank@chagford.com>; +Cc: pgsql-general
> To handle searching for a row based on a string, I will use
> "LOWER(colname)
> = 'x'" and "LOWER(colname) LIKE 'x%'". AFAICT, the second one is
> equivalent
> to "colname ILIKE 'x%'", provided I force 'x' to lowercase first.
I would strongly suggest you do this instead:
LOWER(colname) = LOWER('x')
This is far more bullet-proof than lower-casing in the client, in case
the client and the server differ (mismatched locales, etc.). If you
don't want to use ILIKE for portability reasons (perfectly reasonable),
I suggest the analogous:
LOWER(colname) LIKE LOWER('x%')
- John D. Burger
MITRE
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2005-08-06 09:36 Case sensitivity Frank Millman <frank@chagford.com>
2005-08-08 09:55 ` Re: Case sensitivity Richard Huxton <dev@archonet.com>
2005-08-10 11:18 ` Re: Case sensitivity Frank Millman <frank@chagford.com>
@ 2005-08-10 17:17 ` Roman Neuhauser <neuhauser@sigpipe.cz>
1 sibling, 0 replies; 80+ messages in thread
From: Roman Neuhauser @ 2005-08-10 17:17 UTC (permalink / raw)
To: Frank Millman <frank@chagford.com>; +Cc: pgsql-general
# frank@chagford.com / 2005-08-10 13:18:32 +0200:
> 1. Will SELECT WHERE LOWER(colname) = 'a001' use the index, or must I create
> a separate index on LOWER(colname)?
the latter
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2005-08-06 09:36 Case sensitivity Frank Millman <frank@chagford.com>
2005-08-08 09:55 ` Re: Case sensitivity Richard Huxton <dev@archonet.com>
@ 2005-08-10 13:05 ` Frank Millman <frank@chagford.com>
2 siblings, 0 replies; 80+ messages in thread
From: Frank Millman @ 2005-08-10 13:05 UTC (permalink / raw)
To: pgsql-general
Frank Millman wrote:
> 1. Will SELECT WHERE LOWER(colname) = 'a001' use the index, or must I
create a separate index on LOWER(colname)?
Sorry. RTFM. The docs clearly state that this is necessary.
Frank
^ permalink raw reply [nested|flat] 80+ messages in thread
* Case sensitivity?
@ 2005-12-27 23:54 dfx <dfx@dfx.it>
2005-12-27 15:04 ` Re: Case sensitivity? Lic. Martin Marques <martin@bugs.unl.edu.ar>
2005-12-27 15:14 ` Re: Case sensitivity? Jaime Casanova <systemguards@gmail.com>
2005-12-27 15:14 ` Re: Case sensitivity? John McCawley <nospam@hardgeus.com>
2005-12-27 15:15 ` Re: Case sensitivity? Devrim GUNDUZ <devrim@commandprompt.com>
0 siblings, 4 replies; 80+ messages in thread
From: dfx @ 2005-12-27 23:54 UTC (permalink / raw)
To: pgsql-general
Dear Sirs,
I have a little problem:
- Contest: Server side: Linux Fedora core 4 with PostgreSQL v. 8.0
Client side: both Linux Fedora core 4 with pgAdmin III v.
1.4 and/or Windows 2000 server vith pgAdmin III v. 1.4
- I Made a table:
CREATE TABLE "Accoglienza"
(
"IdAccoglienza" int4 NOT NULL DEFAULT
nextval('public."Accoglienza_IdAccoglienza_seq"'::text),
"IdCongresso" int4 NOT NULL DEFAULT 0,
CONSTRAINT "Accoglienza_Pk" PRIMARY KEY ("IdAccoglienza")
)
WITHOUT OIDS;
if I try "INSERT INTO Accoglienza (IdCongresso) VALUES (23)"
I get an error "ERROR: la relazione "accoglienza" non esiste" (The relation
"accoglienza does not exist")
please note the change of the case of "A" to "a";
if I rename the table to "accoglienza" the error disappear, but arise
another error on "IdAccoglienza" that change in "idaccoglienza". (i.e. all
converted to lower case)
The same error arise with window client and linux client.
How I can set case-insensitive the system, or avoid the conversion to lower
case?
It depends on setting of pgAdmin or on setting of the server?
Thank you.
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity?
2005-12-27 23:54 Case sensitivity? dfx <dfx@dfx.it>
@ 2005-12-27 15:04 ` Lic. Martin Marques <martin@bugs.unl.edu.ar>
3 siblings, 0 replies; 80+ messages in thread
From: Lic. Martin Marques @ 2005-12-27 15:04 UTC (permalink / raw)
To: dfx <dfx@dfx.it>; +Cc: pgsql-general
On Tue, 27 Dec 2005, dfx wrote:
> Dear Sirs,
>
> I have a little problem:
>
> - Contest: Server side: Linux Fedora core 4 with PostgreSQL v. 8.0
> Client side: both Linux Fedora core 4 with pgAdmin III v.
> 1.4 and/or Windows 2000 server vith pgAdmin III v. 1.4
>
> - I Made a table:
>
> CREATE TABLE "Accoglienza"
> (
> "IdAccoglienza" int4 NOT NULL DEFAULT
> nextval('public."Accoglienza_IdAccoglienza_seq"'::text),
> "IdCongresso" int4 NOT NULL DEFAULT 0,
> CONSTRAINT "Accoglienza_Pk" PRIMARY KEY ("IdAccoglienza")
> )
> WITHOUT OIDS;
>
> if I try "INSERT INTO Accoglienza (IdCongresso) VALUES (23)"
>
> I get an error "ERROR: la relazione "accoglienza" non esiste" (The relation
> "accoglienza does not exist")
>
> please note the change of the case of "A" to "a";
>
> if I rename the table to "accoglienza" the error disappear, but arise
> another error on "IdAccoglienza" that change in "idaccoglienza". (i.e. all
> converted to lower case)
>
> The same error arise with window client and linux client.
>
> How I can set case-insensitive the system, or avoid the conversion to lower
> case?
Create the table without closing the relation's name with doble quotes:
CREATE TABLE Accoglienza
(
IdAccoglienza int4 NOT NULL DEFAULT
nextval('public.Accoglienza_IdAccoglienza_seq'::text),
IdCongresso int4 NOT NULL DEFAULT 0,
CONSTRAINT Accoglienza_Pk PRIMARY KEY (IdAccoglienza)
)
WITHOUT OIDS;
--
12:00:02 up 4 days, 23:12, 1 user, load average: 0.35, 0.54, 0.70
---------------------------------------------------------
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática | '@' || 'unl.edu.ar';
Universidad Nacional | DBA, Programador,
del Litoral | Administrador
---------------------------------------------------------
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity?
2005-12-27 23:54 Case sensitivity? dfx <dfx@dfx.it>
@ 2005-12-27 15:14 ` Jaime Casanova <systemguards@gmail.com>
3 siblings, 0 replies; 80+ messages in thread
From: Jaime Casanova @ 2005-12-27 15:14 UTC (permalink / raw)
To: dfx@dfx.it; +Cc: pgsql-general
On 12/27/05, dfx <dfx@dfx.it> wrote:
> Dear Sirs,
>
> I have a little problem:
>
> - Contest: Server side: Linux Fedora core 4 with PostgreSQL v. 8.0
> Client side: both Linux Fedora core 4 with pgAdmin III v.
> 1.4 and/or Windows 2000 server vith pgAdmin III v. 1.4
>
> - I Made a table:
>
> CREATE TABLE "Accoglienza"
> (
> "IdAccoglienza" int4 NOT NULL DEFAULT
> nextval('public."Accoglienza_IdAccoglienza_seq"'::text),
> "IdCongresso" int4 NOT NULL DEFAULT 0,
> CONSTRAINT "Accoglienza_Pk" PRIMARY KEY ("IdAccoglienza")
> )
> WITHOUT OIDS;
>
> if I try "INSERT INTO Accoglienza (IdCongresso) VALUES (23)"
>
> I get an error "ERROR: la relazione "accoglienza" non esiste" (The relation
> "accoglienza does not exist")
>
> please note the change of the case of "A" to "a";
>
> if I rename the table to "accoglienza" the error disappear, but arise
> another error on "IdAccoglienza" that change in "idaccoglienza". (i.e. all
> converted to lower case)
>
> The same error arise with window client and linux client.
>
> How I can set case-insensitive the system, or avoid the conversion to lower
> case?
>
the server always will transfor the names of the object to lower case
unless you surround them with quotes ("")
so if you say
CREATE TABLE TablE --> it will create a table called table
and if you say
CREATE TABLE "TablE" --> it will create a table called "TablE"
and in this case, if you try to select from table it will give an
error... it will give you an error even if you type TablE you have to
type "TablE" or always live with lower case object names...
> It depends on setting of pgAdmin or on setting of the server?
>
> Thank you.
>
>
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity?
2005-12-27 23:54 Case sensitivity? dfx <dfx@dfx.it>
@ 2005-12-27 15:14 ` John McCawley <nospam@hardgeus.com>
2005-12-27 15:29 ` Re: Case sensitivity? Martijn van Oosterhout <kleptog@svana.org>
3 siblings, 1 reply; 80+ messages in thread
From: John McCawley @ 2005-12-27 15:14 UTC (permalink / raw)
To: dfx@dfx.it; +Cc: pgsql-general
Due to case weirdness in Postgres, particularly when accessing it from
PHP, I completely abandoned mixed case table and column names. I don't
know if things have changed in the past several years, but when I first
made the leap, mixed case was more trouble than it was worth.
Even after I got over the initial hump and figured out how to make it
work, there was enough weird behavior (i.e. being able to access a
non-existent array element) in PHP that it led to a lot of bugs when
using mixed-case in Postgres. That's my two cents. Your mileage may
vary...
dfx wrote:
> Dear Sirs,
> <snip>
> please note the change of the case of "A" to "a";
>
> if I rename the table to "accoglienza" the error disappear, but arise
> another error on "IdAccoglienza" that change in "idaccoglienza". (i.e.
> all converted to lower case)
>
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity?
2005-12-27 23:54 Case sensitivity? dfx <dfx@dfx.it>
2005-12-27 15:14 ` Re: Case sensitivity? John McCawley <nospam@hardgeus.com>
@ 2005-12-27 15:29 ` Martijn van Oosterhout <kleptog@svana.org>
2005-12-27 15:56 ` Re: Case sensitivity? John McCawley <nospam@hardgeus.com>
0 siblings, 1 reply; 80+ messages in thread
From: Martijn van Oosterhout @ 2005-12-27 15:29 UTC (permalink / raw)
To: John McCawley <nospam@hardgeus.com>; +Cc: dfx@dfx.it; pgsql-general
On Tue, Dec 27, 2005 at 09:14:20AM -0600, John McCawley wrote:
> Due to case weirdness in Postgres, particularly when accessing it from
> PHP, I completely abandoned mixed case table and column names. I don't
> know if things have changed in the past several years, but when I first
> made the leap, mixed case was more trouble than it was worth.
Well, the rule is very simple, either always quote your identifiers, or
never quote them. If you always quote them, then you always need to
specify them in the same case. If you never quote them, then you always
get case-insensetivity.
> Even after I got over the initial hump and figured out how to make it
> work, there was enough weird behavior (i.e. being able to access a
> non-existent array element) in PHP that it led to a lot of bugs when
> using mixed-case in Postgres. That's my two cents. Your mileage may
> vary...
Hmm, you mean how column titles come back as lowercase? By strict SQL
standard they should come back as all upper-case, but you probably
don't want that either...
SQL has always been case-folding, never case-insensetive.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity?
2005-12-27 23:54 Case sensitivity? dfx <dfx@dfx.it>
2005-12-27 15:14 ` Re: Case sensitivity? John McCawley <nospam@hardgeus.com>
2005-12-27 15:29 ` Re: Case sensitivity? Martijn van Oosterhout <kleptog@svana.org>
@ 2005-12-27 15:56 ` John McCawley <nospam@hardgeus.com>
0 siblings, 0 replies; 80+ messages in thread
From: John McCawley @ 2005-12-27 15:56 UTC (permalink / raw)
To: Martijn van Oosterhout <kleptog@svana.org>; +Cc: dfx@dfx.it; pgsql-general
>Well, the rule is very simple, either always quote your identifiers, or
>never quote them. If you always quote them, then you always need to
>specify them in the same case. If you never quote them, then you always
>get case-insensetivity.
>
>
My memory is a little hazy, as it's been a few years, but I believe my
ultimate problem was that my code ended up being quite confusing. I was
able to use my mixed case, as you said, when I used quotes in the SQL
query (which had to be escaped in PHP), however, the column names came
in all lowercase, so I ended up having to switch between the two,
leading to me making mistakes. See this example (note my example is
using my DB wrapper class):
<?
$obData = GetSQLHelper();
$sSQL = "SELECT \"FooBar\" FROM \"tbl_Foo\"";
$result = $obData->RunSQLReturnRS($sSQL);
$foobar = $result->fields["foobar"];
?>
I generally give my local variables the same name as their database
counterparts, as shown by foobar in this example. The problem is that
foobar ends up as lowercase when it gets back to me. If I get into the
habit of typing "FooBar" mixed case in my SQL queries, it is only a
matter of time before I do the following:
$Foobar = $result->fields["FooBar"];
PHP won't raise any error here, but rather just create a new "FooBar"
variable in the fields map, which is a pretty insidious behavior.
Rather than open myself up to inevitable bugs, I just abandoned mixed
case. I still end up with a similar problem when I misspell column
names, but there isn't a whole lot I can do to avoid that other than
type more carefully.
I know that this is more of a PHP issue than a Postgres issue, but these
days a lot of people are using higher level languages for web
development etc., and can run into similar issues, so I figured I'd
mention this as an argument for avoiding mixed case in the database.
John
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity?
2005-12-27 23:54 Case sensitivity? dfx <dfx@dfx.it>
@ 2005-12-27 15:15 ` Devrim GUNDUZ <devrim@commandprompt.com>
3 siblings, 0 replies; 80+ messages in thread
From: Devrim GUNDUZ @ 2005-12-27 15:15 UTC (permalink / raw)
To: dfx@dfx.it; +Cc: pgsql-general
Hi,
On Tue, 2005-12-27 at 15:54 -0800, dfx wrote:
> How I can set case-insensitive the system, or avoid the conversion to
> lower case?
You need to double-quote the object names that you don't want to be
converted to lower case, like CREATE TABLE "DevrimGunduz".
Regards,
--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: PL/php, plPerlNG - http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 80+ messages in thread
* Case Sensitivity
@ 2011-01-13 14:53 Phillip Smith <phillip@softworks.com>
2011-01-13 15:14 ` Re: Case Sensitivity Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 80+ messages in thread
From: Phillip Smith @ 2011-01-13 14:53 UTC (permalink / raw)
To: pgsql-general
Hi,
I'm moving over from MS SQL. I've been googling this for ages now and suprisingly cannot find a clear answer.
I want my data in tables to be case insensitive.
This is so i can:
1. Put keys on natural key fields, for instance a product part number. I product number 'ABC123' inserted i need to disallow 'abc123' to be inserted as a second row. Please don't tell me i have to add another column holding a lowered version of the product number. My database is littered with this need and i would end up bloating my table schema.
2. I need to query case insensitively. SELECT * FROM product WHERE product_number = 'ABC123' should return the same row as SELECT * FROM product WHERE product_number = 'abc123'
Is there a database wide collation setting i can make, or a case insensitive character type. There are lots of online posts regarding using LOWER function for querying. This is a workaround for point (2) but does not remedy point (1) above.
Many thanks
Phillip
Phillip Smith
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case Sensitivity
2011-01-13 14:53 Case Sensitivity Phillip Smith <phillip@softworks.com>
@ 2011-01-13 15:14 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-01-13 15:25 ` Re: Case Sensitivity Phillip Smith <phillip@softworks.com>
2011-01-13 15:25 ` Re: Case Sensitivity Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 2 replies; 80+ messages in thread
From: Pavel Stehule @ 2011-01-13 15:14 UTC (permalink / raw)
To: Phillip Smith <phillip@softworks.com>; +Cc: pgsql-general
Hello
you should to use a citext datatype
http://www.postgresql.org/docs/9.0/interactive/citext.html
regards
Pavel Stehule
2011/1/13 Phillip Smith <phillip@softworks.com>:
> Hi,
> I'm moving over from MS SQL. I've been googling this for ages now and
> suprisingly cannot find a clear answer.
>
> I want my data in tables to be case insensitive.
>
> This is so i can:
> 1. Put keys on natural key fields, for instance a product part number. I
> product number 'ABC123' inserted i need to disallow 'abc123' to be inserted
> as a second row. Please don't tell me i have to add another column holding a
> lowered version of the product number. My database is littered with this
> need and i would end up bloating my table schema.
>
> 2. I need to query case insensitively. SELECT * FROM product WHERE
> product_number = 'ABC123' should return the same row as SELECT * FROM
> product WHERE product_number = 'abc123'
>
> Is there a database wide collation setting i can make, or a case
> insensitive character type. There are lots of online posts regarding using
> LOWER function for querying. This is a workaround for point (2) but does not
> remedy point (1) above.
>
> Many thanks
>
> Phillip
>
> Phillip Smith
>
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case Sensitivity
2011-01-13 14:53 Case Sensitivity Phillip Smith <phillip@softworks.com>
2011-01-13 15:14 ` Re: Case Sensitivity Pavel Stehule <pavel.stehule@gmail.com>
@ 2011-01-13 15:25 ` Phillip Smith <phillip@softworks.com>
2011-01-13 15:39 ` Re: Case Sensitivity Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 1 reply; 80+ messages in thread
From: Phillip Smith @ 2011-01-13 15:25 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-general
Thanks Pavel,
Is this the only way?. I would prefer to use a collation setting if this is possible. Do you know whether there would be a performance hit using non standard character strings?
Phillip
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case Sensitivity
2011-01-13 14:53 Case Sensitivity Phillip Smith <phillip@softworks.com>
2011-01-13 15:14 ` Re: Case Sensitivity Pavel Stehule <pavel.stehule@gmail.com>
2011-01-13 15:25 ` Re: Case Sensitivity Phillip Smith <phillip@softworks.com>
@ 2011-01-13 15:39 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 80+ messages in thread
From: Pavel Stehule @ 2011-01-13 15:39 UTC (permalink / raw)
To: Phillip Smith <phillip@softworks.com>; +Cc: pgsql-general
2011/1/13 Phillip Smith <phillip@softworks.com>:
> Thanks Pavel,
>
> Is this the only way?. I would prefer to use a collation setting if this is
> possible. Do you know whether there would be a performance hit using non
> standard character strings?
It is preferred way. PostgreSQL doesn't support a collations, and what
I know collation in 9.1 will not allow a comparation between case
sensitive and case insensitive text, because collation in PostgreSQL
is based on unix locale, and there are not case insensitive locale.
I don't know about performance problems. It does same work like case
insensitive collation.
Regards
Pavel Stehule
>
> Phillip
>
>
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case Sensitivity
2011-01-13 14:53 Case Sensitivity Phillip Smith <phillip@softworks.com>
2011-01-13 15:14 ` Re: Case Sensitivity Pavel Stehule <pavel.stehule@gmail.com>
@ 2011-01-13 15:25 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2011-01-13 15:29 ` Re: Case Sensitivity Phillip Smith <phillip@softworks.com>
1 sibling, 1 reply; 80+ messages in thread
From: Karsten Hilbert @ 2011-01-13 15:25 UTC (permalink / raw)
To: pgsql-general
> you should to use a citext datatype
>
> http://www.postgresql.org/docs/9.0/interactive/citext.html
Or:
- use a functional index with lower() to ensure uniqueness
- use a BEFORE trigger to lower() the inserted data
Karsten
--
GMX DSL Doppel-Flat ab 19,99 Euro/mtl.! Jetzt mit
gratis Handy-Flat! http://portal.gmx.net/de/go/dsl
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case Sensitivity
2011-01-13 14:53 Case Sensitivity Phillip Smith <phillip@softworks.com>
2011-01-13 15:14 ` Re: Case Sensitivity Pavel Stehule <pavel.stehule@gmail.com>
2011-01-13 15:25 ` Re: Case Sensitivity Karsten Hilbert <Karsten.Hilbert@gmx.net>
@ 2011-01-13 15:29 ` Phillip Smith <phillip@softworks.com>
2011-01-13 16:18 ` Re: Case Sensitivity Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 80+ messages in thread
From: Phillip Smith @ 2011-01-13 15:29 UTC (permalink / raw)
To: Karsten Hilbert <Karsten.Hilbert@gmx.net>; pgsql-general
Hi Karsten,
modifying user inputted data is not an option for me. I need to maintain the original data. I had read about indexing on a lower function. This all seems a bit of a bodgy workaround to me. A clean Case Insensitive collation setting is cleaner. Is this supported in postgreSQL 9?
Regards
Phillip
Phillip
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case Sensitivity
2011-01-13 14:53 Case Sensitivity Phillip Smith <phillip@softworks.com>
2011-01-13 15:14 ` Re: Case Sensitivity Pavel Stehule <pavel.stehule@gmail.com>
2011-01-13 15:25 ` Re: Case Sensitivity Karsten Hilbert <Karsten.Hilbert@gmx.net>
2011-01-13 15:29 ` Re: Case Sensitivity Phillip Smith <phillip@softworks.com>
@ 2011-01-13 16:18 ` Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 80+ messages in thread
From: Andrew Sullivan @ 2011-01-13 16:18 UTC (permalink / raw)
To: pgsql-general
On Thu, Jan 13, 2011 at 03:29:03PM -0000, Phillip Smith wrote:
> modifying user inputted data is not an option for me. I need to
> maintain the original data. I had read about indexing on a lower
> function. This all seems a bit of a bodgy workaround to me. A clean
> Case Insensitive collation setting is cleaner. Is this supported in
> postgreSQL 9?
There isn't such a collation setting as far as I know, and it's
actually not as clean as you think. It turns out to work reasonably
well in ASCII-land, but not very well in other circumstances. In
general, the case-insensitive but case-preserving technique that
English-speaking computer users have come to find normal is a bad fit
for a wide variety of languages. (Even "Latin" characterset-using
languages have trouble, because of historic ways of handling accents.
Is the capital version of é E or É? Well, both, it turns out,
depending on whom you believe. I could bore you about the effects of
this in the DNS all day long, but I'll resist the temptation.)
Putting an index on lower(column) and then doing all your comparisons
with lower(datum) works fairly well, and I don't see how it's any more
bodgy than a database-wide case insensitive collation. For instance,
I can assure you that customers named Leblanc and LeBlanc care about
whether those two compare equally. In your customer name field, if
you have a database-wide collation setting, you can't make the
distinction.
A
--
Andrew Sullivan
ajs@crankycanuck.ca
^ permalink raw reply [nested|flat] 80+ messages in thread
* case sensitivity?
@ 2011-06-06 20:24 Heine Ferreira <heine.ferreira@gmail.com>
2011-06-06 20:29 ` Re: case sensitivity? Scott Marlowe <scott.marlowe@gmail.com>
2011-06-06 20:29 ` Re: case sensitivity? Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 2 replies; 80+ messages in thread
From: Heine Ferreira @ 2011-06-06 20:24 UTC (permalink / raw)
To: pgsql-general
Hi
I posted a similar question before. I was told that Postgres is always case
sensitive.
I was actually referring to text data not identifiers.
I mean char and varchar fields.
I basicically want to avoid using upper in comparisons.
With some databases you can use a utf8 characterset and a unicode or win1252
coallation.
Has anyone tried this?
Do you know if this will work?
Thanks
H.F.
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: case sensitivity?
2011-06-06 20:24 case sensitivity? Heine Ferreira <heine.ferreira@gmail.com>
@ 2011-06-06 20:29 ` Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 80+ messages in thread
From: Scott Marlowe @ 2011-06-06 20:29 UTC (permalink / raw)
To: Heine Ferreira <heine.ferreira@gmail.com>; +Cc: pgsql-general
On Mon, Jun 6, 2011 at 2:24 PM, Heine Ferreira <heine.ferreira@gmail.com> wrote:
> Hi
>
> I posted a similar question before. I was told that Postgres is always case
> sensitive.
> I was actually referring to text data not identifiers.
> I mean char and varchar fields.
> I basicically want to avoid using upper in comparisons.
> With some databases you can use a utf8 characterset and a unicode or win1252
> coallation.
> Has anyone tried this?
> Do you know if this will work?
Maybe the citext type would do what you want?
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: case sensitivity?
2011-06-06 20:24 case sensitivity? Heine Ferreira <heine.ferreira@gmail.com>
@ 2011-06-06 20:29 ` Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 0 replies; 80+ messages in thread
From: Andrew Sullivan @ 2011-06-06 20:29 UTC (permalink / raw)
To: pgsql-general
On Mon, Jun 06, 2011 at 10:24:45PM +0200, Heine Ferreira wrote:
> I basicically want to avoid using upper in comparisons.
> Has anyone tried this?
> Do you know if this will work?
There's a contrib module that will allow you to do this. See the
citext datatype.
A
--
Andrew Sullivan
ajs@crankycanuck.ca
^ permalink raw reply [nested|flat] 80+ messages in thread
* Case sensitivity
@ 2013-12-11 06:31 Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-11 06:40 ` Re: Case sensitivity John R Pierce <pierce@hogranch.com>
0 siblings, 1 reply; 80+ messages in thread
From: Dev Kumkar @ 2013-12-11 06:31 UTC (permalink / raw)
To: pgsql-general
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] 80+ messages in thread
* Re: Case sensitivity
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 ` Re: Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
0 siblings, 1 reply; 80+ messages in thread
From: John R Pierce @ 2013-12-11 06:40 UTC (permalink / raw)
To: pgsql-general
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] 80+ messages in thread
* Re: Case sensitivity
2013-12-11 06:31 Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-11 06:40 ` Re: Case sensitivity John R Pierce <pierce@hogranch.com>
@ 2013-12-11 11:25 ` Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-11 15:16 ` Re: Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-11 15:28 ` Re: Case sensitivity Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 2 replies; 80+ messages in thread
From: Dev Kumkar @ 2013-12-11 11:25 UTC (permalink / raw)
To: John R Pierce <pierce@hogranch.com>; +Cc: pgsql-general
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] 80+ messages in thread
* Re: Case sensitivity
2013-12-11 06:31 Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-11 06:40 ` Re: Case sensitivity John R Pierce <pierce@hogranch.com>
2013-12-11 11:25 ` Re: Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
@ 2013-12-11 15:16 ` Dev Kumkar <devdas.kumkar@gmail.com>
1 sibling, 0 replies; 80+ messages in thread
From: Dev Kumkar @ 2013-12-11 15:16 UTC (permalink / raw)
To: John R Pierce <pierce@hogranch.com>; +Cc: pgsql-general
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] 80+ messages in thread
* Re: Case sensitivity
2013-12-11 06:31 Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-11 06:40 ` Re: Case sensitivity John R Pierce <pierce@hogranch.com>
2013-12-11 11:25 ` Re: Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
@ 2013-12-11 15:28 ` Andrew Sullivan <ajs@crankycanuck.ca>
2013-12-11 16:17 ` Re: Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
1 sibling, 1 reply; 80+ messages in thread
From: Andrew Sullivan @ 2013-12-11 15:28 UTC (permalink / raw)
To: pgsql-general
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] 80+ messages in thread
* Re: Case sensitivity
2013-12-11 06:31 Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-11 06:40 ` Re: Case sensitivity John R Pierce <pierce@hogranch.com>
2013-12-11 11:25 ` Re: Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-11 15:28 ` Re: Case sensitivity Andrew Sullivan <ajs@crankycanuck.ca>
@ 2013-12-11 16:17 ` Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-12 07:04 ` Re: Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
0 siblings, 1 reply; 80+ messages in thread
From: Dev Kumkar @ 2013-12-11 16:17 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-general
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] 80+ messages in thread
* Re: Case sensitivity
2013-12-11 06:31 Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-11 06:40 ` Re: Case sensitivity John R Pierce <pierce@hogranch.com>
2013-12-11 11:25 ` Re: Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-11 15:28 ` Re: Case sensitivity Andrew Sullivan <ajs@crankycanuck.ca>
2013-12-11 16:17 ` Re: Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
@ 2013-12-12 07:04 ` Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-12 07:17 ` Re: Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
0 siblings, 1 reply; 80+ messages in thread
From: Dev Kumkar @ 2013-12-12 07:04 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-general
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] 80+ messages in thread
* Re: Case sensitivity
2013-12-11 06:31 Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-11 06:40 ` Re: Case sensitivity John R Pierce <pierce@hogranch.com>
2013-12-11 11:25 ` Re: Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-11 15:28 ` Re: Case sensitivity Andrew Sullivan <ajs@crankycanuck.ca>
2013-12-11 16:17 ` Re: Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-12 07:04 ` Re: Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
@ 2013-12-12 07:17 ` Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-13 07:08 ` Re: [GENERAL] Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
0 siblings, 1 reply; 80+ messages in thread
From: Dev Kumkar @ 2013-12-12 07:17 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-general; 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] 80+ messages in thread
* Re: [GENERAL] Case sensitivity
2013-12-11 06:31 Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-11 06:40 ` Re: Case sensitivity John R Pierce <pierce@hogranch.com>
2013-12-11 11:25 ` Re: Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-11 15:28 ` Re: Case sensitivity Andrew Sullivan <ajs@crankycanuck.ca>
2013-12-11 16:17 ` Re: Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-12 07:04 ` Re: Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
2013-12-12 07:17 ` Re: Case sensitivity Dev Kumkar <devdas.kumkar@gmail.com>
@ 2013-12-13 07:08 ` Dev Kumkar <devdas.kumkar@gmail.com>
0 siblings, 0 replies; 80+ messages in thread
From: Dev Kumkar @ 2013-12-13 07:08 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>; +Cc: pgsql-general; 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] 80+ messages in thread
* Case sensitivity
@ 2025-12-23 22:08 Igor Korot <ikorot01@gmail.com>
2025-12-23 22:23 ` Re: Case sensitivity Adrian Klaver <adrian.klaver@aklaver.com>
2025-12-24 00:23 ` Re: Case sensitivity David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 80+ messages in thread
From: Igor Korot @ 2025-12-23 22:08 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Hi, ALL,
In the https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS,
there is no indication whether those values are case sensitive or not.
Thank you,
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2025-12-23 22:08 Case sensitivity Igor Korot <ikorot01@gmail.com>
@ 2025-12-23 22:23 ` Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 0 replies; 80+ messages in thread
From: Adrian Klaver @ 2025-12-23 22:23 UTC (permalink / raw)
To: Igor Korot <ikorot01@gmail.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On 12/23/25 14:08, Igor Korot wrote:
> Hi, ALL,
> In the https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS,
> there is no indication whether those values are case sensitive or not.
Easy enough to test:
psql postgresql://aklaver@localhost/test
Null display is "NULL".
psql (18.1 (Ubuntu 18.1-1.pgdg22.04+2), server 17.7 (Ubuntu
17.7-3.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
compression: off, ALPN: postgresql)
Type "help" for help.
test=>
VS
psql postgresql://aklaver@localhost/TEST
psql: error: connection to server at "localhost" (127.0.0.1), port 5432
failed: FATAL: database "TEST" does not exist
>
> Thank you,
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2025-12-23 22:08 Case sensitivity Igor Korot <ikorot01@gmail.com>
@ 2025-12-24 00:23 ` David G. Johnston <david.g.johnston@gmail.com>
2025-12-24 01:03 ` Re: Case sensitivity Igor Korot <ikorot01@gmail.com>
1 sibling, 1 reply; 80+ messages in thread
From: David G. Johnston @ 2025-12-24 00:23 UTC (permalink / raw)
To: Igor Korot <ikorot01@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On Tue, Dec 23, 2025 at 3:08 PM Igor Korot <ikorot01@gmail.com> wrote:
> Hi, ALL,
> In the
> https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS
> ,
> there is no indication whether those values are case sensitive or not.
>
>
One should assume case-sensitivity unless otherwise stated.
David J.
^ permalink raw reply [nested|flat] 80+ messages in thread
* Re: Case sensitivity
2025-12-23 22:08 Case sensitivity Igor Korot <ikorot01@gmail.com>
2025-12-24 00:23 ` Re: Case sensitivity David G. Johnston <david.g.johnston@gmail.com>
@ 2025-12-24 01:03 ` Igor Korot <ikorot01@gmail.com>
0 siblings, 0 replies; 80+ messages in thread
From: Igor Korot @ 2025-12-24 01:03 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Thank you ALL.
On Tue, Dec 23, 2025 at 4:23 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Tue, Dec 23, 2025 at 3:08 PM Igor Korot <ikorot01@gmail.com> wrote:
>>
>> Hi, ALL,
>> In the https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS,
>> there is no indication whether those values are case sensitive or not.
>>
>
> One should assume case-sensitivity unless otherwise stated.
>
> David J.
>
^ permalink raw reply [nested|flat] 80+ messages in thread
end of thread, other threads:[~2025-12-24 01:03 UTC | newest]
Thread overview: 80+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-09-20 03:06 Case sensitivity Dana Powers <powersdp@ctrvax.Vanderbilt.Edu>
1999-09-20 03:43 Case sensitivity Dana Powers <powersdp@ctrvax.Vanderbilt.Edu>
1999-09-20 04:05 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-09-20 15:58 ` Dana Powers <dana@fphome.com>
1999-09-20 16:32 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-09-20 17:04 ` Dana Powers <dana@fphome.com>
1999-09-21 04:43 ` Charles Tassell <ctassell@isn.net>
2001-02-27 05:47 Case sensitivity Bruce Richardson <brichard@cafod.org.uk>
2001-02-27 15:07 ` Oliver Elphick <olly@lfix.co.uk>
2001-02-27 16:08 ` Joel Burton <jburton@scw.org>
2001-05-12 10:23 case sensitivity Simon Crute <simon-news@nospam.geordie.demon.co.uk>
2001-05-13 03:09 ` Re: case sensitivity Tom Lane <tgl@sss.pgh.pa.us>
2001-05-13 10:21 ` Re: case sensitivity Simon Crute <simon-news@nospam.geordie.demon.co.uk>
2001-05-14 21:14 ` Re: Re: case sensitivity newsreader@mediaone.net
2001-05-14 22:49 ` Re: Re: case sensitivity Gilles DAROLD <gilles@darold.net>
2001-08-14 06:09 Case sensitivity Scott Gilbert <scott.gilbert@netregistry.au.com>
2001-08-14 06:47 ` John Clark Naldoza y Lopez <njclark@ntsp.nec.co.jp>
2001-08-14 04:25 ` Digital Wokan <wokan@home.com>
2001-08-26 21:06 case sensitivity? Robert J. Sanford, Jr. <rsanford@nolimitsystems.com>
2001-08-27 03:35 ` Re: case sensitivity? hubert depesz lubaczewski <depesz@depesz.pl>
2002-06-20 14:20 Case sensitivity Varun Kacholia <varunk@cse.iitb.ac.in>
2002-06-20 15:13 ` Andrew Sullivan <andrew@libertyrms.info>
2002-06-20 15:16 ` Darren Ferguson <darren@crystalballinc.com>
2004-01-11 09:29 Case sensitivity Dario Ottaviano <dario_ottaviano@togliquestovirgilio.it>
2004-01-12 09:57 ` Richard Huxton <dev@archonet.com>
2004-01-12 17:58 ` scott.marlowe <scott.marlowe@ihs.com>
2004-01-12 22:22 ` Richard Huxton <dev@archonet.com>
2004-01-12 22:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-09 23:01 Case sensitivity Jason Tesser <JTesser@nbbc.edu>
2004-02-10 06:02 ` David Garamond <lists@zara.6.isreserved.com>
2004-02-10 06:33 ` Klint Gore <kg@kgb.une.edu.au>
2004-02-10 16:04 ` Andrew Sullivan <ajs@crankycanuck.ca>
2005-06-10 12:22 Case sensitivity Howard Cole <howard.cole@selestial.com>
2005-06-12 18:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-13 08:32 ` Howard Cole <howardnews@selestial.com>
2005-06-12 18:32 Re: Case sensitivity Magnus Hagander <mha@sollentuna.net>
2005-06-13 08:52 Re: Case sensitivity Magnus Hagander <mha@sollentuna.net>
2005-06-13 09:34 ` Howard Cole <howardnews@selestial.com>
2005-08-06 09:36 Case sensitivity Frank Millman <frank@chagford.com>
2005-08-08 09:55 ` Richard Huxton <dev@archonet.com>
2005-08-09 07:35 ` Frank Millman <frank@chagford.com>
2005-08-09 09:57 ` Martijn van Oosterhout <kleptog@svana.org>
2005-08-09 11:04 ` Martijn van Oosterhout <kleptog@svana.org>
2005-08-09 15:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-09 15:54 ` Martijn van Oosterhout <kleptog@svana.org>
2005-08-09 11:38 ` Richard Huxton <dev@archonet.com>
2005-08-10 11:18 ` Frank Millman <frank@chagford.com>
2005-08-10 13:53 ` John D. Burger <john@mitre.org>
2005-08-10 17:17 ` Roman Neuhauser <neuhauser@sigpipe.cz>
2005-08-10 13:05 ` Frank Millman <frank@chagford.com>
2005-12-27 23:54 Case sensitivity? dfx <dfx@dfx.it>
2005-12-27 15:04 ` Re: Case sensitivity? Lic. Martin Marques <martin@bugs.unl.edu.ar>
2005-12-27 15:14 ` Re: Case sensitivity? Jaime Casanova <systemguards@gmail.com>
2005-12-27 15:14 ` Re: Case sensitivity? John McCawley <nospam@hardgeus.com>
2005-12-27 15:29 ` Re: Case sensitivity? Martijn van Oosterhout <kleptog@svana.org>
2005-12-27 15:56 ` Re: Case sensitivity? John McCawley <nospam@hardgeus.com>
2005-12-27 15:15 ` Re: Case sensitivity? Devrim GUNDUZ <devrim@commandprompt.com>
2011-01-13 14:53 Case Sensitivity Phillip Smith <phillip@softworks.com>
2011-01-13 15:14 ` Re: Case Sensitivity Pavel Stehule <pavel.stehule@gmail.com>
2011-01-13 15:25 ` Re: Case Sensitivity Phillip Smith <phillip@softworks.com>
2011-01-13 15:39 ` Re: Case Sensitivity Pavel Stehule <pavel.stehule@gmail.com>
2011-01-13 15:25 ` Re: Case Sensitivity Karsten Hilbert <Karsten.Hilbert@gmx.net>
2011-01-13 15:29 ` Re: Case Sensitivity Phillip Smith <phillip@softworks.com>
2011-01-13 16:18 ` Re: Case Sensitivity Andrew Sullivan <ajs@crankycanuck.ca>
2011-06-06 20:24 case sensitivity? Heine Ferreira <heine.ferreira@gmail.com>
2011-06-06 20:29 ` Re: case sensitivity? Scott Marlowe <scott.marlowe@gmail.com>
2011-06-06 20:29 ` Re: case sensitivity? Andrew Sullivan <ajs@crankycanuck.ca>
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>
2025-12-23 22:08 Case sensitivity Igor Korot <ikorot01@gmail.com>
2025-12-23 22:23 ` Adrian Klaver <adrian.klaver@aklaver.com>
2025-12-24 00:23 ` David G. Johnston <david.g.johnston@gmail.com>
2025-12-24 01:03 ` Igor Korot <ikorot01@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