pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCase Sensitive "WHERE" Clauses?
13+ messages / 9 participants
[nested] [flat]
* Case Sensitive "WHERE" Clauses?
@ 2002-09-26 17:54 Jordan Reiter <jordan@breezing.com>
0 siblings, 5 replies; 13+ messages in thread
From: Jordan Reiter @ 2002-09-26 17:54 UTC (permalink / raw)
To: pgsql-sql
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 successfully 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Case Sensitive "WHERE" Clauses?
@ 2002-09-26 18:22 Andrew Perrin <clists@perrin.socsci.unc.edu>
parent: Jordan Reiter <jordan@breezing.com>
4 siblings, 1 reply; 13+ messages in thread
From: Andrew Perrin @ 2002-09-26 18:22 UTC (permalink / raw)
To: Jordan Reiter <jordan@breezing.com>; +Cc: pgsql-sql
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 used successfully 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
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Case Sensitive "WHERE" Clauses?
@ 2002-09-26 20:57 Dan Langille <dan@langille.org>
parent: Andrew Perrin <clists@perrin.socsci.unc.edu>
0 siblings, 0 replies; 13+ messages in thread
From: Dan Langille @ 2002-09-26 20:57 UTC (permalink / raw)
To: Andrew Perrin <clists@perrin.socsci.unc.edu>; +Cc: Jordan Reiter <jordan@breezing.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Case Sensitive "WHERE" Clauses?
@ 2002-09-26 21:08 Dan Langille <dan@langille.org>
parent: Jordan Reiter <jordan@breezing.com>
4 siblings, 0 replies; 13+ messages in thread
From: Dan Langille @ 2002-09-26 21:08 UTC (permalink / raw)
To: Jordan Reiter <jordan@breezing.com>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Case Sensitive "WHERE" Clauses?
@ 2002-09-26 23:00 Ian Barwick <barwick@gmx.net>
parent: Jordan Reiter <jordan@breezing.com>
4 siblings, 0 replies; 13+ messages in thread
From: Ian Barwick @ 2002-09-26 23:00 UTC (permalink / raw)
To: Jordan Reiter <jordan@breezing.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Case Sensitive "WHERE" Clauses?
@ 2002-09-26 23:14 Chris <csmith@squiz.net>
parent: Jordan Reiter <jordan@breezing.com>
4 siblings, 1 reply; 13+ messages in thread
From: Chris @ 2002-09-26 23:14 UTC (permalink / raw)
To: Ian Barwick <barwick@gmx.net>; pgsql-sql
>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)
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Case Sensitive "WHERE" Clauses?
@ 2002-09-26 23:33 Ian Barwick <barwick@gmx.net>
parent: Chris <csmith@squiz.net>
0 siblings, 3 replies; 13+ messages in thread
From: Ian Barwick @ 2002-09-26 23:33 UTC (permalink / raw)
To: Chris <csmith@squiz.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Case Sensitive "WHERE" Clauses?
@ 2002-09-26 23:41 Jochem van Dieten <nomail@devnull.invalid>
parent: Ian Barwick <barwick@gmx.net>
2 siblings, 0 replies; 13+ messages in thread
From: Jochem van Dieten @ 2002-09-26 23:41 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Case Sensitive "WHERE" Clauses?
@ 2002-09-27 00:17 Jordan Reiter <jordan@breezing.com>
parent: Ian Barwick <barwick@gmx.net>
2 siblings, 0 replies; 13+ messages in thread
From: Jordan Reiter @ 2002-09-27 00:17 UTC (permalink / raw)
To: Ian Barwick <barwick@gmx.net>; Chris <csmith@squiz.net>; pgsql-sql
> > > > 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. You have 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Case Sensitive "WHERE" Clauses?
@ 2002-09-27 03:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ian Barwick <barwick@gmx.net>
2 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2002-09-27 03:19 UTC (permalink / raw)
To: Ian Barwick <barwick@gmx.net>; +Cc: Chris <csmith@squiz.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Case Sensitive "WHERE" Clauses?
@ 2002-09-27 07:25 Ian Barwick <barwick@gmx.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Ian Barwick @ 2002-09-27 07:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Case Sensitive "WHERE" Clauses?
@ 2002-09-27 07:37 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Ian Barwick <barwick@gmx.net>
0 siblings, 0 replies; 13+ messages in thread
From: Stephan Szabo @ 2002-09-27 07:37 UTC (permalink / raw)
To: Ian Barwick <barwick@gmx.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Case Sensitive "WHERE" Clauses?
@ 2002-09-28 03:38 Kevin Houle <kevin@houle.org>
parent: Jordan Reiter <jordan@breezing.com>
4 siblings, 0 replies; 13+ messages in thread
From: Kevin Houle @ 2002-09-28 03:38 UTC (permalink / raw)
To: Jordan Reiter <jordan@breezing.com>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2002-09-28 03:38 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-26 17:54 Case Sensitive "WHERE" Clauses? Jordan Reiter <jordan@breezing.com>
2002-09-26 18:22 ` Andrew Perrin <clists@perrin.socsci.unc.edu>
2002-09-26 20:57 ` Dan Langille <dan@langille.org>
2002-09-26 21:08 ` Dan Langille <dan@langille.org>
2002-09-26 23:00 ` Ian Barwick <barwick@gmx.net>
2002-09-26 23:14 ` Chris <csmith@squiz.net>
2002-09-26 23:33 ` Ian Barwick <barwick@gmx.net>
2002-09-26 23:41 ` Jochem van Dieten <nomail@devnull.invalid>
2002-09-27 00:17 ` Jordan Reiter <jordan@breezing.com>
2002-09-27 03:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-09-27 07:25 ` Ian Barwick <barwick@gmx.net>
2002-09-27 07:37 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-09-28 03:38 ` Kevin Houle <kevin@houle.org>
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