[Resolved] does PostgreSQL do case-insensitive string matching by default?

[Bloodwine]

I noticed that I have a SQL query like:

select <data> from users where uid='$uid'

in MySQL, the match is case-insensitive, so "JoeUser" and "joeuser" both succeed

does PostgreSQL do the same, or is it case-sensitive by default?

furthermore, is it better to do a "LIKE" instead of a "=" when dealing with VARCHAR fields? I'm looking for an exact, case-insensitive match.

[Sxooter]

Nope, PostgreSQL is case sensitive by default.

However, it can do case insensitive matches in a couple of ways:

select from table where field ILIKE 'abc';
select from table where upper(field) like 'ABC'

Note that depending on your locale, either one of these may have issues with characters that have accents on them...

[Bloodwine]

Your second query:

select * from table where upper(field) like 'ABC'

looks like it'd work on MySQL as well. I checked the MySQL manual and it supports UPPER() and LOWER()

Thanks for the help.

[Sxooter]

Also, upper(field) is indexable on postgresql (assuming you're using the C locale):

create index tbl_upper_field_dx on table (upper(field));