Thread: Case Sensitive "WHERE" Clauses?

Case Sensitive "WHERE" Clauses?

From
Jordan Reiter
Date:
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


Re: Case Sensitive "WHERE" Clauses?

From
Andrew Perrin
Date:
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
> 



Re: Case Sensitive "WHERE" Clauses?

From
Dan Langille
Date:
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.



Re: Case Sensitive "WHERE" Clauses?

From
Dan Langille
Date:
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)



Re: Case Sensitive "WHERE" Clauses?

From
Ian Barwick
Date:
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





Re: Case Sensitive "WHERE" Clauses?

From
Chris
Date:

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



Re: Case Sensitive "WHERE" Clauses?

From
Ian Barwick
Date:
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





Re: Case Sensitive "WHERE" Clauses?

From
Jordan Reiter
Date:
> > > > 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


Re: Case Sensitive "WHERE" Clauses?

From
Tom Lane
Date:
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


Re: Case Sensitive "WHERE" Clauses?

From
Ian Barwick
Date:
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



Re: Case Sensitive "WHERE" Clauses?

From
Stephan Szabo
Date:
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)




Re: Case Sensitive "WHERE" Clauses?

From
Jochem van Dieten
Date:
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



Re: Case Sensitive "WHERE" Clauses?

From
Kevin Houle
Date:
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