6 ms·

Or they want to allow for case-insensitivity of some data, like for example email addresses on login forms. As much as postgres is overall better than MySQL in

Or they want to allow for case-insensitivity of some data, like for example email addresses on login forms. As much as postgres is overall better than MySQL in so many ways, it's still ridiculously difficult to set things up such that SELECT id FROM users WHERE email='foo@example.com' returns the same result as SELECT id FROM users WHERE email='Foo@example.com'

synotna 9y ago
WHERE email ILIKE 'foo@example.com' https://www.postgresql.org/docs/9.6/static/functions-matching.html https://www.postgresql.org/docs/9.6/static/functions-matchin...
fphilipe 9y ago
Does MySQL do that on varchar by default? Can't you just do this in PostgreSQL? SELECT id FROM users WHERE email = lower('Foo@example.com')
sametmax 9y ago
Just lowercase everything. Not that hard.
- irrational 9y ago
  That only works if you are only dealing with English. I've posted a comment with a solution that works across all languages.
- brianwawok 9y ago
  Uppercase is the master race
rarrrrrr 9y ago
Here's an example of doing that in PostgreSQL: create table user_email ( email text not null ); -- create a index on the lowercase form -- of the email create unique index user_email_case_idx on user_email (lower(email)); -- select using the index, with the lowercase form. select 1 from user_email where lower(email)=lower('Foo@foo.com');
- olavgg 9y ago
  actually it is easier SELECT 1 FROM user_email WHERE email ILIKE 'Foo@Foo.coM';
  - always_good 9y ago
    WHERE lower(email) = lower('foo@example.com') Is simple and hits an index on lower(email). I'm not sure ILIKE can hit an index in your example.
    - olavgg 9y ago
      It can if you use the pg_trgm extension, a good summary can be read here: https://niallburkley.com/blog/index-columns-for-like-in-postgres/ https://niallburkley.com/blog/index-columns-for-like-in-post...
ncd 9y ago
Postgres also has the citext column type to make this a snap. https://www.postgresql.org/docs/9.6/static/citext.html https://www.postgresql.org/docs/9.6/static/citext.html
grahamedgecombe 9y ago
The citext type automatically does case-insensitive comparisons: https://www.postgresql.org/docs/current/static/citext.html https://www.postgresql.org/docs/current/static/citext.html
thejosh 9y ago
Because you don't always want to match 'Foo' to 'foo'?
- jakobegger 9y ago
  That's what per-column collations are for. Ideally you should be able to choose from case sensitive and case insensitive collations. Unfortunately PostgreSQL doesn't support case insensitive collations (for some reason the string comparison routines use memcmp as a tie-breaker when the collation says strings are equal).
metalliqaz 9y ago
I'm no Postgres master by any means, but I searched it: https://duckduckgo.com/?q=postgres+case+insensitive+query https://duckduckgo.com/?q=postgres+case+insensitive+query solution immediately came up at SO: SELECT id FROM users WHERE LOWER(email)=LOWER('Foo@example.com')
- irrational 9y ago
  That only works if you are only dealing with English. I've posted a comment with a solution that works across all languages.
mgkimsal 9y ago
not sure why you got downvoted so much. Everyone's answer is "just lowercase everything". I'll respond just a bit: 1. You don't always have control over all the queries that have been written against your database. 2. You would probably lose the ability to use ORMs without a moderate amount of customization. 3. If you're migrating from a different database, you may have checksums on your data that would all need to be recalculated if you change case on everything stored. 4. Doing runtime lowercase() on everything adds a bit of overhead, doesn't it? citext on postgresql seems a decent option - the citext docs even mention drawbacks of some of the other recommended options.
- openasocket 9y ago
  Is there a single ORM out there that doesn't support the lower() function? I googled "case insensitive search" + a couple ORMs and each of them could implement it as a one-liner. And doing the runtime lower() on everything will generally not be slower than citext. If you look at the source for the citext comparison (https://github.com/postgres/postgres/blob/aa9eac45ea868e6ddabc4eb076d18be10ce84c6a/contrib/citext/citext.c#L113 https://github.com/postgres/postgres/blob/aa9eac45ea868e6dda...) you'll see it is internally converting the values to lowercase and comparing them. All it saves you is the overhead of a sql function invocation, and you'd have to do a lot of comparisons to make that difference measurable. But if you're doing a lot of those comparisons, unless you're just running the calculation on the same couple values over and over, the memory and disk latency will dominate performance, not the minimal overhead of the sql function invocation. I agree you should probably use citext if you need case-insensitive unique or primary key values, but be aware of the drawbacks. https://www.postgresql.org/docs/current/static/citext.html https://www.postgresql.org/docs/current/static/citext.html
- solidsnack9000 9y ago
  > 4. Doing runtime lowercase() on everything adds a bit of overhead, doesn't it? Maybe MySQL has special sauce for doing this comparison without lowercasing the query string? But there must be some overhead relative to exact search?
irrational 9y ago
If you want to do case-insensitive for all languages you can do this: 1. first install the following (be sure to replace [your schema]: CREATE EXTENSION pg_trgm with schema extension; CREATE EXTENSION unaccent with schema extension; CREATE OR REPLACE FUNCTION insensitive_query(text) RETURNS text AS $func$ SELECT lower([your schema].unaccent('[your schema].unaccent', $1)) $func$ LANGUAGE sql IMMUTABLE; 2. then in your query you can use: where insensitive_query(my_table.name) LIKE insensitive_query('Bob')
- openasocket 9y ago
  That will not work for all languages. Look at https://www.w3.org/International/wiki/Case_folding https://www.w3.org/International/wiki/Case_folding for an explanation of why this problem is nontrivial. The lower function is sufficient: it handles case-folding properly, using the configured locale. Explicitly stripping accents can actually be the wrong choice depending on the locale
inopinatus 9y ago
That's a bad practice. Did you know: email addresses are case-sensitive on the left-hand-side. It's discouraged by RFC5321 whilst also being defined by it.

synotna 9y ago

WHERE email ILIKE 'foo@example.com' https://www.postgresql.org/docs/9.6/static/functions-matching.html https://www.postgresql.org/docs/9.6/static/functions-matchin...

fphilipe 9y ago

Does MySQL do that on varchar by default? Can't you just do this in PostgreSQL? SELECT id FROM users WHERE email = lower('Foo@example.com')

sametmax 9y ago

Just lowercase everything. Not that hard.

irrational 9y ago
That only works if you are only dealing with English. I've posted a comment with a solution that works across all languages.
brianwawok 9y ago
Uppercase is the master race

rarrrrrr 9y ago

Here's an example of doing that in PostgreSQL: create table user_email ( email text not null ); -- create a index on the lowercase form -- of the email create unique index user_email_case_idx on user_email (lower(email)); -- select using the index, with the lowercase form. select 1 from user_email where lower(email)=lower('Foo@foo.com');

olavgg 9y ago
actually it is easier SELECT 1 FROM user_email WHERE email ILIKE 'Foo@Foo.coM';
- always_good 9y ago
  WHERE lower(email) = lower('foo@example.com') Is simple and hits an index on lower(email). I'm not sure ILIKE can hit an index in your example.
  - olavgg 9y ago
    It can if you use the pg_trgm extension, a good summary can be read here: https://niallburkley.com/blog/index-columns-for-like-in-postgres/ https://niallburkley.com/blog/index-columns-for-like-in-post...

ncd 9y ago

Postgres also has the citext column type to make this a snap. https://www.postgresql.org/docs/9.6/static/citext.html https://www.postgresql.org/docs/9.6/static/citext.html

grahamedgecombe 9y ago

The citext type automatically does case-insensitive comparisons: https://www.postgresql.org/docs/current/static/citext.html https://www.postgresql.org/docs/current/static/citext.html

thejosh 9y ago

Because you don't always want to match 'Foo' to 'foo'?

jakobegger 9y ago
That's what per-column collations are for. Ideally you should be able to choose from case sensitive and case insensitive collations. Unfortunately PostgreSQL doesn't support case insensitive collations (for some reason the string comparison routines use memcmp as a tie-breaker when the collation says strings are equal).

metalliqaz 9y ago

I'm no Postgres master by any means, but I searched it: https://duckduckgo.com/?q=postgres+case+insensitive+query https://duckduckgo.com/?q=postgres+case+insensitive+query solution immediately came up at SO: SELECT id FROM users WHERE LOWER(email)=LOWER('Foo@example.com')

irrational 9y ago
That only works if you are only dealing with English. I've posted a comment with a solution that works across all languages.

mgkimsal 9y ago

not sure why you got downvoted so much. Everyone's answer is "just lowercase everything". I'll respond just a bit: 1. You don't always have control over all the queries that have been written against your database. 2. You would probably lose the ability to use ORMs without a moderate amount of customization. 3. If you're migrating from a different database, you may have checksums on your data that would all need to be recalculated if you change case on everything stored. 4. Doing runtime lowercase() on everything adds a bit of overhead, doesn't it? citext on postgresql seems a decent option - the citext docs even mention drawbacks of some of the other recommended options.

openasocket 9y ago
Is there a single ORM out there that doesn't support the lower() function? I googled "case insensitive search" + a couple ORMs and each of them could implement it as a one-liner. And doing the runtime lower() on everything will generally not be slower than citext. If you look at the source for the citext comparison (https://github.com/postgres/postgres/blob/aa9eac45ea868e6ddabc4eb076d18be10ce84c6a/contrib/citext/citext.c#L113 https://github.com/postgres/postgres/blob/aa9eac45ea868e6dda...) you'll see it is internally converting the values to lowercase and comparing them. All it saves you is the overhead of a sql function invocation, and you'd have to do a lot of comparisons to make that difference measurable. But if you're doing a lot of those comparisons, unless you're just running the calculation on the same couple values over and over, the memory and disk latency will dominate performance, not the minimal overhead of the sql function invocation. I agree you should probably use citext if you need case-insensitive unique or primary key values, but be aware of the drawbacks. https://www.postgresql.org/docs/current/static/citext.html https://www.postgresql.org/docs/current/static/citext.html
solidsnack9000 9y ago
> 4. Doing runtime lowercase() on everything adds a bit of overhead, doesn't it? Maybe MySQL has special sauce for doing this comparison without lowercasing the query string? But there must be some overhead relative to exact search?

irrational 9y ago

If you want to do case-insensitive for all languages you can do this: 1. first install the following (be sure to replace [your schema]: CREATE EXTENSION pg_trgm with schema extension; CREATE EXTENSION unaccent with schema extension; CREATE OR REPLACE FUNCTION insensitive_query(text) RETURNS text AS $func$ SELECT lower([your schema].unaccent('[your schema].unaccent', $1)) $func$ LANGUAGE sql IMMUTABLE; 2. then in your query you can use: where insensitive_query(my_table.name) LIKE insensitive_query('Bob')

openasocket 9y ago
That will not work for all languages. Look at https://www.w3.org/International/wiki/Case_folding https://www.w3.org/International/wiki/Case_folding for an explanation of why this problem is nontrivial. The lower function is sufficient: it handles case-folding properly, using the configured locale. Explicitly stripping accents can actually be the wrong choice depending on the locale

inopinatus 9y ago

That's a bad practice. Did you know: email addresses are case-sensitive on the left-hand-side. It's discouraged by RFC5321 whilst also being defined by it.