Re: PostgreSQL Case Sensitive Data?
Dave Merrill wrote: Frankly, I agree with Matt that search behavior really should be case insensitive. Are we talking about searching or equality matching? Because frankly, I don't think there are any 2 databases that have compatible search systems. If, like most other dbs, searching for calculated expressions (LCASE(some_column) = 'foo' etc) is significantly inefficient It isn't if you create the right index: CREATE INDEX foo ON bar (LOWER(some_column)); Are folks really sure that no case-insensitive collation option is available? I don't think so. PostgreSQL uses the collation facilities provided by the host OS so maybe there is one on some OS but I never looked for one. Jochem ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266560 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: PostgreSQL Case Sensitive Data?
Matt Quackenbush wrote: Now, I know that the table names and columns are case sensitive, and I already took care of all of those issues. But data? Case sensitive data?? I'm actually in shock over this one. Really? Have you ever looked close at CF? Try the following code: cfscript qryTest = QueryNew(str); queryAddRow(qryTest, 3); querySetCell(qryTest, str, 'a', 1); querySetCell(qryTest, str, 'B', 2); querySetCell(qryTest, str, 'c', 3); /cfscript cfquery name=qryDemo dbtype=query SELECT * FROM qryTest ORDER BY str /cfquery cfdump var=#qryTest# cfdump var=#qryDemo# While this is an 'okay' solution for a search form, I think it's insane to have to resort to the following for a login query: SELECT userid FROM tbl_user WHERE user_name ILIKE '#form.user_name#' AND user_psswd LIKE '#form.user_psswd#'; I think it is insane to second-guess the user on his imput. There are few things that annoy me more then stupid systems that will fully automatically 'correct' my last name from 'van Dieten' to 'Van Dieten' or even 'VanDieten'. Again, my question is, and I'm praying hard that the answer is a resounding 'YES!', but is there a way to make the data case INsensitive? You are asking the wrong question :) Data is case sensitive. Always. In every database. If you put data in in some case, you don't get it back in some random other case, you get it back in the same case you put it in. And that is a good thing because case has meaning, a MB is very different from a mb. Some operations on data can be case insensitive. So your solution is not case insensitive data, your solution is a case insensitve operator. Luckily it is very easy to define your own operators in PostgreSQL. For instance, this defines case-insensitive equality and inequality operators for text datatypes: CREATE FUNCTION case_insensitive_equality(text, text) RETURNS boolean AS 'SELECT LOWER($1) = LOWER($2)' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT; CREATE FUNCTION case_insensitive_inequality(text, text) RETURNS boolean AS 'SELECT LOWER($1) LOWER($2)' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT; CREATE OPERATOR === ( leftarg = text, rightarg = text, procedure = case_insensitive_equality, commutator = ===, negator= = ); CREATE OPERATOR = ( leftarg = text, rightarg = text, procedure = case_insensitive_inequality, commutator = =, negator= === ); I would recommend against overwriting the current = and operators with these operators because I have no idea what the side-effects may be and I don't think they are indexable. Jochem ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266491 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: PostgreSQL Case Sensitive Data?
Jochem, Dave,
My disbelief ('shock' is really too harsh a word for me to have used) comes
from the fact that I'm a brain-washed Windoze guy - one might even say that
I have a Microca$h property patch on me - who has never (to my recollection)
in 9+ years worked with a SQL Server collation that was anything other than
case INsensitive. I'm trying to get out more these days, and venture into
the real world, the one in which you guys have long since been thriving.
:-)
Mind you, I am absolutely NOT a DBA. My database experience is 100% in
using them for web-based applications.
Jochem, I am responding inline to your other comments...
On 1/13/07, Jochem van Dieten [EMAIL PROTECTED] wrote:
Matt Quackenbush wrote:
Now, I know that the table names and columns are case sensitive, and I
already took care of all of those issues. But data? Case sensitive
data??
I'm actually in shock over this one.
Really? Have you ever looked close at CF? Try the following code:
cfscript
qryTest = QueryNew(str);
queryAddRow(qryTest, 3);
querySetCell(qryTest, str, 'a', 1);
querySetCell(qryTest, str, 'B', 2);
querySetCell(qryTest, str, 'c', 3);
/cfscript
cfquery name=qryDemo dbtype=query
SELECT *
FROM qryTest
ORDER BY str
/cfquery
cfdump var=#qryTest#
cfdump var=#qryDemo#
In regards to QoQ being case sensitive, yes, I am well aware of that
behavior. And it has always been something of an annoyance to me. I have
always used lower(myfield) and #lCase(myval)# to get around that. These
discussions here though are making me reconsider my position. Again, my
disbelief is more because of inexperience in such an environment than it is
refusal to open my mind and think that there's any other way.
While this is an 'okay' solution for a search form, I think it's insane to
have to resort to the following for a login query:
SELECT userid FROM tbl_user
WHERE user_name ILIKE '#form.user_name#'
AND user_psswd LIKE '#form.user_psswd#';
I think it is insane to second-guess the user on his imput. There are few
things that
annoy me more then stupid systems that will fully automatically 'correct'
my last name from 'van Dieten' to 'Van Dieten' or even 'VanDieten'.
Call me spoiled, but in the user name example, I want my user name to appear
on-screen in mixed case, but I thoroughly enjoy being able to be lazy and
enter it in all lower case when logging in. As far as passwords go,
absolutely, they should *always* be case sensitive. But I've always
believed in having case rules be managed by the app, not the database
server. I am trying hard to re-think that, and to understand the benefits
of it. You guys are helping me with that. Thank you!
Again, my question is, and I'm praying hard that the answer is a
resounding
'YES!', but is there a way to make the data case INsensitive?
You are asking the wrong question :)
Data is case sensitive. Always. In every database. If you put data in in
some case, you don't get it back in some random other case, you get it back
in the same case you put it in. And that is a good thing because case has
meaning, a MB is very different from a mb.
I thoroughly agree that data should be returned in the same case in which it
was entered, in regards to display. But for search strings, when you are
searching on something that you did NOT enter, or hell, maybe you entered it
6 or 8 months ago (e.g. a blog post), a case sensitive search does not seem
to be appropriate. On the contrary, it seems like that would be a source of
frustration for the end user who gets no results, but they know that the
info which they seek is there somewhere. Being forced to figure out the
case it was entered in would, I think, drive many (most?) users away.
Some operations on data can be case insensitive. So your solution is not
case insensitive data, your solution is a case insensitve operator. Luckily
it is very easy to define your own operators in PostgreSQL. For instance,
this defines case-insensitive equality and inequality operators for text
datatypes:
CREATE FUNCTION case_insensitive_equality(text, text) RETURNS boolean
AS 'SELECT LOWER($1) = LOWER($2)'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
CREATE FUNCTION case_insensitive_inequality(text, text) RETURNS boolean
AS 'SELECT LOWER($1) LOWER($2)'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
CREATE OPERATOR === (
leftarg = text,
rightarg = text,
procedure = case_insensitive_equality,
commutator = ===,
negator= =
);
CREATE OPERATOR = (
leftarg = text,
rightarg = text,
procedure = case_insensitive_inequality,
commutator = =,
negator= ===
);
Wow! That's very cool. I haven't gotten to that part of the manual yet. I
have so very much yet to learn about the real world. :-)
I would recommend against overwriting the current = and
Re: PostgreSQL Case Sensitive Data?
Frankly, I agree with Matt that search behavior really should be case insensitive. I've wanted to check out postgres for a while, but finding this out may well slow me down. If, like most other dbs, searching for calculated expressions (LCASE(some_column) = 'foo' etc) is significantly inefficient, how are you supposed to manage this in the real world? Only idea I had was to store every possibly searchable value in mixed case for display, and somewhere else in all lowercase for searching. The other thing is that this makes it hard to use postgres as a drop-in replacement for most other common dbs. Sure, you could (re-)design the app from scratch to handle what most other dbs usually do automatically, but if that's really what you have to do, it seems like a much bigger decision than dealing with the much less pervasive syntax differences between vendors. Are folks really sure that no case-insensitive collation option is available? Dave ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266513 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: PostgreSQL Case Sensitive Data?
I was directed to the following link, which appears to have promise, but haven't researched it enough yet to know. http://gborg.postgresql.org/project/citext/projdisplay.php As far as a case-insensitive collation, I've found absolutely nothing that suggests you can do so on an imported database. As was mentioned by Dave in a previous response, you *might* be able to use LC_Collate when creating a new database, but I haven't found a definitive answer on that yet. Still searching. Matt On 1/13/07, Dave Merrill [EMAIL PROTECTED] wrote: Frankly, I agree with Matt that search behavior really should be case insensitive. I've wanted to check out postgres for a while, but finding this out may well slow me down. If, like most other dbs, searching for calculated expressions (LCASE(some_column) = 'foo' etc) is significantly inefficient, how are you supposed to manage this in the real world? Only idea I had was to store every possibly searchable value in mixed case for display, and somewhere else in all lowercase for searching. The other thing is that this makes it hard to use postgres as a drop-in replacement for most other common dbs. Sure, you could (re-)design the app from scratch to handle what most other dbs usually do automatically, but if that's really what you have to do, it seems like a much bigger decision than dealing with the much less pervasive syntax differences between vendors. Are folks really sure that no case-insensitive collation option is available? Dave ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266514 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
PostgreSQL Case Sensitive Data?
Hello, After successfully importing my entire MS SQL database and having the Pg server up and flying, I decided to make a copy of my site and test everything out. I went to the login screen, filled in my user name and password and submitted. After several unsuccessful attempts, it dawned on me what the problem was: I was entering my user name in all lower case, while in the database it has mIxEd cAsE. So, I tried again, using the mixed case. Boom! Login works. Now, I know that the table names and columns are case sensitive, and I already took care of all of those issues. But data? Case sensitive data?? I'm actually in shock over this one. Anyways, shock aside, is there some way that I can make it not be case sensitive with the data? While this is an 'okay' solution for a search form, I think it's insane to have to resort to the following for a login query: SELECT userid FROM tbl_user WHERE user_name ILIKE '#form.user_name#' AND user_psswd LIKE '#form.user_psswd#'; or this one: SELECT userid FROM tbl_user WHERE lower(user_name) = '#lcase(form.user_name)#' AND lower(user_psswd) = '#lcase(form.user_psswd)#'; (Yes, I actually use cfqueryparam / in a real query... just didn't want to type out the whole thing.) Again, my question is, and I'm praying hard that the answer is a resounding 'YES!', but is there a way to make the data case INsensitive? Thanks in advance, Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266476 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: PostgreSQL Case Sensitive Data?
Matt, There's no way that I know of. Just my 2 cents, but I think that case sensitivity with data is a wonderful thing - especially when it comes to user authentication. (http://en.wikipedia.org/wiki/ Password_strength) I think you'll probably have to handle that within the query - or just change the username and password to lowercase. Jon On Jan 12, 2007, at 11:46 PM, Matt Quackenbush wrote: Hello, After successfully importing my entire MS SQL database and having the Pg server up and flying, I decided to make a copy of my site and test everything out. I went to the login screen, filled in my user name and password and submitted. After several unsuccessful attempts, it dawned on me what the problem was: I was entering my user name in all lower case, while in the database it has mIxEd cAsE. So, I tried again, using the mixed case. Boom! Login works. Now, I know that the table names and columns are case sensitive, and I already took care of all of those issues. But data? Case sensitive data?? I'm actually in shock over this one. Anyways, shock aside, is there some way that I can make it not be case sensitive with the data? While this is an 'okay' solution for a search form, I think it's insane to have to resort to the following for a login query: SELECT userid FROM tbl_user WHERE user_name ILIKE '#form.user_name#' AND user_psswd LIKE '#form.user_psswd#'; or this one: SELECT userid FROM tbl_user WHERE lower(user_name) = '#lcase(form.user_name)#' AND lower(user_psswd) = '#lcase(form.user_psswd)#'; (Yes, I actually use cfqueryparam / in a real query... just didn't want to type out the whole thing.) Again, my question is, and I'm praying hard that the answer is a resounding 'YES!', but is there a way to make the data case INsensitive? Thanks in advance, Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266482 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: PostgreSQL Case Sensitive Data?
Jon, I wholeheartedly agree that a password should be a case sensitive check, without a doubt. My apps always do that. But that kind of illustrates my point: *my apps handle it*. I believe that case rules should fall on the shoulders of the app, not the database. I have no problem whatsoever with there being a case sensitive datatype, or an option on the field/column to make it case sensitive... hell, I'd fully support that. But to just blanketly make data case sensitive? (Sorry, I'm still in a state of shock. :-) ) For instance, take a search form. I think it's insane to expect people to use the same case in their search string as was entered into the database. I was really hoping there was a way around that behavior, without modifying all of my queries. Oh well, I guess either A) I'll have to continue to bite the MS SQL $$$ bullet, or B) I'll have to write a second set of files that are set to handle the case-sensitive nature of Pg. Thanks for the help! Matt On 1/13/07, Jon Clausen [EMAIL PROTECTED] wrote: Matt, There's no way that I know of. Just my 2 cents, but I think that case sensitivity with data is a wonderful thing - especially when it comes to user authentication. (http://en.wikipedia.org/wiki/ Password_strength) I think you'll probably have to handle that within the query - or just change the username and password to lowercase. Jon ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266483 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: PostgreSQL Case Sensitive Data?
Now, I know that the table names and columns are case sensitive, and I already took care of all of those issues. But data? Case sensitive data?? I'm actually in shock over this one. Anyways, shock aside, is there some way that I can make it not be case sensitive with the data? You shouldn't be shocked. Case-sensitive collations are quite common. In SQL Server, while case-insensitive collations are used by default, you can choose all sorts of collation options. As for PostgreSQL specifically, it doesn't appear to be especially easy to define this. I'm no PostgreSQL expert, but you might be able to define a custom locale, and set the LC_COLLATE environment variable using initdb - I think you can only do this for new databases. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266485 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
