Re: 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 successfully before? Obviously, formatting the search string for the query is not a solution...
--

Jordan Reiter mailto:jordan(at)breezing(dot)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(at)perrin(dot)socsci(dot)unc(dot)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(at)breezing(dot)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(at)postgresql(dot)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(at)gmx(dot)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(at)gmx(dot)net

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

> > > > 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(at)breezing(dot)com
Breezing.com http://breezing.com
1106 West Main St phone:434.295.2050
Charlottesville, VA 22903 fax:603.843.6931

Ian Barwick <barwick(at)gmx(dot)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(at)gmx(dot)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(at)gmx(dot)net

On Fri, 27 Sep 2002, Ian Barwick wrote:

> On Friday 27 September 2002 05:19, Tom Lane wrote:
> > Ian Barwick <barwick(at)gmx(dot)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)

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