Thread: Case Sensitive "WHERE" Clauses?
Are string comparisons in postgresql case sensitive? I keep on having this response: SELECT * FROM People WHERE first_name='jordan' Result: 0 records SELECT * FROM People WHERE first_name='Jordan' Result: 1 record I though that string matching in SQL was case-insensitive. Isn't this correct? If not, what workarounds have been used successfullybefore? Obviously, formatting the search string for the query is not a solution... -- Jordan Reiter mailto:jordan@breezing.com Breezing.com http://breezing.com 1106 West Main St phone:434.295.2050 Charlottesville, VA 22903 fax:603.843.6931
No, I don't think it's supposed to be case-sensitive. In any case, whether it's supposed to be or not, it certainly isn't in practice. Solutions include: SELECT * FROM People WHERE lower(first_name)='jordan'; and: SELECT * FROM People WHERE first_name ~* 'Jordan'; ap ---------------------------------------------------------------------- Andrew J Perrin - http://www.unc.edu/~aperrin Assistant Professor of Sociology, U of North Carolina, Chapel Hill clists@perrin.socsci.unc.edu * andrew_perrin (at) unc.edu On Thu, 26 Sep 2002, Jordan Reiter wrote: > Are string comparisons in postgresql case sensitive? > > I keep on having this response: > > SELECT * > FROM People > WHERE first_name='jordan' > > Result: 0 records > > SELECT * > FROM People > WHERE first_name='Jordan' > > Result: 1 record > > I though that string matching in SQL was case-insensitive. Isn't this correct? If not, what workarounds have been usedsuccessfully before? Obviously, formatting the search string for the query is not a solution... > -- > > Jordan Reiter mailto:jordan@breezing.com > Breezing.com http://breezing.com > 1106 West Main St phone:434.295.2050 > Charlottesville, VA 22903 fax:603.843.6931 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
On Thu, 26 Sep 2002, Andrew Perrin wrote: > No, I don't think it's supposed to be case-sensitive. In any case, whether > it's supposed to be or not, it certainly isn't in practice. AFAIK, they are case sensitive by design. It is the right thing to do.
On Thu, 26 Sep 2002, Jordan Reiter wrote: > Are string comparisons in postgresql case sensitive? Yes, AFAIK. I disagree with your comments and recommendations posted at http://www.postgresql.org/idocs/index.php?datatype-character.html because my testing shows that varying text and fixed test comparisons are both case sensitive. testing=# \d casetest Table "casetest"Column | Type | Modifiers --------+---------------+-----------name | text |city | character(10) | testing=# select * from casetest;name | city ------+------------Dan | Ottawa (1 row) testing=# select * from casetest where name = 'Dan';name ------Dan (1 row) testing=# select * from casetest where name = 'dan';name ------ (0 rows) testing=# select * from casetest where city = 'ottawa';name | city ------+------ (0 rows)
On Thursday 26 September 2002 19:54, Jordan Reiter wrote:
> Are string comparisons in postgresql case sensitive?
Yes, unless you specify otherwise.
Are you sure you are using the right database? I can
reproduce similar results, but only like this:
mysql> create temporary table foo (ch char(2), vc varchar(2));
Query OK, 0 rows affected (0.12 sec)
mysql> insert into foo values ('aa','AA');
Query OK, 1 row affected (0.02 sec)
mysql> select * from foo where ch = 'aa';
+------+------+
| ch | vc |
+------+------+
| aa | AA |
+------+------+
1 row in set (0.01 sec)
mysql> select * from foo where ch = 'AA';
+------+------+
| ch | vc |
+------+------+
| aa | AA |
+------+------+
1 row in set (0.00 sec)
mysql> select * from foo where vc = 'aa';
+------+------+
| ch | vc |
+------+------+
| aa | AA |
+------+------+
1 row in set (0.00 sec)
Regards
Ian Barwick
barwick@gmx.net
>On Thursday 26 September 2002 19:54, Jordan Reiter wrote:
> > Are string comparisons in postgresql case sensitive?
>
>Yes, unless you specify otherwise.
>
>Are you sure you are using the right database? I can
>reproduce similar results, but only like this:
You're using MySQL in these examples .. not Postgres :)
(FYI - Just tried this with 7.3beta and I got the same results as everyone
else .. it is case sensitive).
Chris.
>mysql> create temporary table foo (ch char(2), vc varchar(2));
>Query OK, 0 rows affected (0.12 sec)
>
>mysql> insert into foo values ('aa','AA');
>Query OK, 1 row affected (0.02 sec)
>
>mysql> select * from foo where ch = 'aa';
>+------+------+
>| ch | vc |
>+------+------+
>| aa | AA |
>+------+------+
>1 row in set (0.01 sec)
>
>mysql> select * from foo where ch = 'AA';
>+------+------+
>| ch | vc |
>+------+------+
>| aa | AA |
>+------+------+
>1 row in set (0.00 sec)
>
>mysql> select * from foo where vc = 'aa';
>+------+------+
>| ch | vc |
>+------+------+
>| aa | AA |
>+------+------+
>1 row in set (0.00 sec)
>
On Friday 27 September 2002 01:14, Chris wrote: > >On Thursday 26 September 2002 19:54, Jordan Reiter wrote: > > > Are string comparisons in postgresql case sensitive? > > > >Yes, unless you specify otherwise. > > > >Are you sure you are using the right database? I can > >reproduce similar results, but only like this: > > You're using MySQL in these examples .. not Postgres :) Full points for paying attention ;-) This, erm, characteristic of the former caused me a lot of grief once... Anyone know what the ANSI standard is? I don`t recall any other database apart from MySQL which default to case-insensitive CHAR or VARCHAR columns. Ian Barwick barwick@gmx.net
> > > > Are string comparisons in postgresql case sensitive? >> > >> >Yes, unless you specify otherwise. >> > >> >Are you sure you are using the right database? I can >> >reproduce similar results, but only like this: >> >> You're using MySQL in these examples .. not Postgres :) > >Full points for paying attention ;-) > >This, erm, characteristic of the former caused me a lot of grief once... > >Anyone know what the ANSI standard is? I don`t recall any other >database apart from MySQL which default to case-insensitive >CHAR or VARCHAR columns. Microsoft Products (SQL Server, Access) are case-insensitive. I find it hard to understand why it's advantageous that column names are NOT case sensitive, while field content is. Youhave a *lot* more control over the database columns than you do over the content that goes into the fields. In my opinion,allowing someone to refer to a column as first_name, First_Name, or FIRST_NAME just encourages bad programming. -- Jordan Reiter mailto:jordan@breezing.com Breezing.com http://breezing.com 1106 West Main St phone:434.295.2050 Charlottesville, VA 22903 fax:603.843.6931
Ian Barwick <barwick@gmx.net> writes:
> Anyone know what the ANSI standard is? I don`t recall any other
> database apart from MySQL which default to case-insensitive
> CHAR or VARCHAR columns.
I believe the spec has a notion of a "collation attribute" attached
to character-type columns. You could define a collation that makes
comparisons case insensitive and then mark selected columns that way.
We don't have anything like that yet, though Tatsuo has been heard
muttering about how to make it happen ...
regards, tom lane
On Friday 27 September 2002 05:19, Tom Lane wrote: > Ian Barwick <barwick@gmx.net> writes: > > Anyone know what the ANSI standard is? I don`t recall any other > > database apart from MySQL which default to case-insensitive > > CHAR or VARCHAR columns. > > I believe the spec has a notion of a "collation attribute" attached > to character-type columns. You could define a collation that makes > comparisons case insensitive and then mark selected columns that way. > We don't have anything like that yet, though Tatsuo has been heard > muttering about how to make it happen ... For reference, MySQL treats CHAR and VARCHAR columns as case insensitive by default; to be treated as case sensitive, fields must be defined or redefined as CHAR BINARY / VARCHAR BINARY. Personally I prefer handling case (in)sensitivity explicitly in the WHERE clause or at application level, though if the standard allows it and it's optional, enabling specific columns to be case insensitive in comparisions can only be a Good Thing (TM). Ian Barwick barwick@gmx.net
On Fri, 27 Sep 2002, Ian Barwick wrote: > On Friday 27 September 2002 05:19, Tom Lane wrote: > > Ian Barwick <barwick@gmx.net> writes: > > > Anyone know what the ANSI standard is? I don`t recall any other > > > database apart from MySQL which default to case-insensitive > > > CHAR or VARCHAR columns. > > > > I believe the spec has a notion of a "collation attribute" attached > > to character-type columns. You could define a collation that makes > > comparisons case insensitive and then mark selected columns that way. > > We don't have anything like that yet, though Tatsuo has been heard > > muttering about how to make it happen ... > > For reference, MySQL treats CHAR and VARCHAR columns as > case insensitive by default; to be treated as case sensitive, fields > must be defined or redefined as CHAR BINARY / VARCHAR BINARY. > > Personally I prefer handling case (in)sensitivity explicitly in the WHERE > clause or at application level, though if the standard allows it and it's > optional, enabling specific columns to be case insensitive in comparisions > can only be a Good Thing (TM). AFAICT it's not only a table column thing, it's all the way through, most times you're specifying a character string of some sort of or another you can attach an explicit collation with COLLATE. The rules for how this all works look fairly arcane though. (As an example, it looks like group by can get them so you might be able to say "group by col1 COLLATE foo" in order to use the foo collation in order to do the grouping)
Ian Barwick wrote: > > Anyone know what the ANSI standard is? I don`t recall any other > database apart from MySQL which default to case-insensitive > CHAR or VARCHAR columns. SQL:1999 says collation dependent. Jochem
Jordan Reiter wrote: > Are string comparisons in postgresql case sensitive? > > I keep on having this response: > > SELECT * > FROM People > WHERE first_name='jordan' > > Result: 0 records > > SELECT * > FROM People > WHERE first_name='Jordan' > > Result: 1 record It's case-sensitive. You can do this: SELECT * FROM People WHERE lower(first_name) = 'jordon' Kevin