LOWER - Case-Insensitive İşlem İçin Kullanılabilir

Giriş

Açıklaması şöyle

To perform case-insensitive queries, use ILIKE, regular expressions, or the lower() function on both sides of the comparison operator. If lower() is used frequently, the column should be indexed using the case_insensitive collation since otherwise, the index will not be used. 

LOWER ve Index İlişkisi

Açıklaması şöyle

When the Lower function is used, the indexes on the relevant column become unusable. Each search will be done as a “sequential scan.” This will cause performance problems as the number of rows increases. “Expressional Index” is recommended as a solution. For details, see https://www.postgresql.org/docs/9.1/indexes-expressional.html.

Örnek - Unique Index With LOWER

Açıklaması şöyle

For example, a common way to do case-insensitive comparisons is to use the lower function:

SELECT * FROM test1 WHERE lower(col1) = 'value';

This query can use an index if one has been defined on the result of the lower(col1) function:

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

If we were to declare this index UNIQUE, it would prevent creation of rows whose col1 values differ only in case, as well as rows whose col1 values are actually identical. Thus, indexes on expressions can be used to enforce constraints that are not definable as simple unique constraints.

Örnek - Case Insensitive Unique Index With Collation

Açıklaması şöyle

If lower() is used frequently, the column should be indexed using the case_insensitive collation since otherwise, the index will not be used. 

Şöyle yaparız

/*See https://www.postgresql.org/docs/current/collation.html#COLLATION-NONDETERMINISTIC */
CREATE COLLATION case_insensitive
(provider = icu, locale='und-u-ks-level2', deterministic=false);

Sonra şöyle yaparız

CREATE UNIQUE INDEX users_email_unique ON users(email COLLATE case_insensitive);

İndeks'in kullanıldığını görmek için şöyle yaparız

EXPLAIN
SELECT * FROM users
WHERE lower(email) = lower('Seed-system-user')
AND deleted_at IS NULL;

Çıktı şöyle olmalı

-> Bitmap Index Scan on users_email_unique

Kullanım

Örnek

Şöyle yaparız

SELECT * FROM test1 WHERE lower(col1) = lower('value');