PostgreSQL Case Sensitivity

4 min readFeb 27, 2023

Büyük-küçük harf duyarlılığı (case sensitivity) konusu veritabanı geliştirmesi yaparken karşımıza sıkça çıkar. Hangi veritabanı teknolojisini kullanırsak kullanalım uygulamamızın belirli yerlerinde büyük-küçük harfe duyarlı olmak (case sensitive) gerekirken, bazı yerlerinde ise büyük veya küçük harf kullanımı bir fark yaratmamaktadır (case insensitive). Bu durum ise filtreleme yaparken karşımıza sıkça çıkar. Örneğin, “İSTANBUL” ile “istanbul” birbirine eşit midir, değil midir? Veritabanı teknolojilerinin bu konuya varsayılan yaklaşımları ise birinden farklılık gösterebilir. Örneğin, MSSQL default olarak case insensitive iken, PostgreSQL case sensitive’dir. Bu yazıda PostgreSQL kullandığımız durumda case insensitivity için hangi çözümlere başvurabiliriz onu inceleyeceğiz.

Collation

Collation bir dil veya alfabenin karakter kullanımını tanımlar. Eşitliklerde, sıralamalarda, büyük-küçük harf ayrımlarında karakterlerin nasıl kullanılacağını belirler.

PostgreSQL dokümanlarındaki tanımı ise şöyledir:

The collation feature allows specifying the sort order and character classification behavior of data per-column, or even per-operation.

24.2. Collation Support

The collation feature allows specifying the sort order and character classification behavior of data per-column, or…

www.postgresql.org

Collation kavramını en basit haliyle veritabanı dilini belirlerken kullanıyoruz. Collation veritabanı seviyesinde, tablo seviyesinde, sütun seviyesinde ve sorgu seviyesinde kullanılabilir.

Dikkat edilmesi gereken noktalar:

Npgsql dokümantasyonuna göre; collation desteği çok yeni ve tamamlanmamış olduğu için dikkatli kullanılmalıdır. Collation’a alternatif tüm seçenekler incelenmelidir. Detaylı bilgi için https://www.npgsql.org/efcore/misc/collations-and-case-sensitivity.html?tabs=data-annotations.
Collation deterministic ve non-deterministic olarak yapılandırılabilir. Deterministic olanlar uzun süredir kullanılmasına rağmen non-deterministic PostgreSQL versiyon 12 ile gelmiştir. Case insensitivity non-deterministic collation’lar ile birlikte geldiği için kullanılan versiyona dikkat etmek gerekmektedir.
En kritik dezavantajlarından biri de non-deterministic collation kullanıldığı durumda pattern matching fonksiyonları (örn: LIKE) kullanılamaz hale geliyor.

Lower Fonksiyonu

Uygulanabilecek en temel çözümlerden biridir. Aranan değer ve değerin arandığı sütun lowerfonksiyonundan geçirilir. Filtreleme küçük harfler üzerinden yapılarak case insensitive bir kıyas yapılmış olur.

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

Dikkat edilmesi gereken noktalar:

Lower fonksiyonu kullanıldığında ilgili sütun üzerindeki indexler kullanılmaz hale gelir. Her bir arama “sequential scan” olarak yapılacaktır. Bu da satır sayısı arttıkça performans sorunları ortaya çıkaracaktır. Çözüm olarak “Expressional Index” önerilmektedir. Detaylar için https://www.postgresql.org/docs/9.1/indexes-expressional.html
Case insensitive filtreleme gerektiren her sorguda bu yöntem uygulanması, tekrar tekrar benzer işin yapılması demektir. Çünkü data access katmanındaki sorgularda case insensitive çözüm için sürekli olarak Lower fonksiyonu kullanılacaktır. Geliştirme sırasında hem kod tekrarına düşülecek hem de kolayca gözden kaçabilecektir.
Lower fonksiyonu verilen değeri küçük harfe çevirirken veritabanının collation bilgisini baz alır. Dile özel büyük-küçük harf farklılıkları hatalara sebebiyet verebilir. Örneğin, “TR” için “F-I-L-E” kelimesinin küçük harf yazımı “f-ı-l-e” iken, “EN” için “F-I-L-E” kelimesinin küçük harf yazımı “f-i-l-e” olacaktır.

Citext Extension

Citext PostgreSQLde extension olarak eklenebilen bir veri tipidir. Case insensitive text anlamına gelir. Trusted bir extension olduğu için basitçe eklenebilir.

CREATE EXTENSION IF NOT EXISTS citext;

Entity Framework Core (EFCore) kullanıldığı durumda DbContext OnModelCreating methodu içerisinde modelBuilder.HasPostgresExtension("citext"); şeklinde eklenebilir. Sonrasında migration oluşturulması gerekir.

Citext veri tipinin çalışma mantığı Lower fonksiyonu ile aynıdır. İmplementasyonunda Lower fonksiyonu çağırılmaktadır. Dolayısıyla yukarıda Lower fonksiyonu için yazılan tüm maddeler citext için de geçerlidir. En büyük avantajı ilgili sütunu case insensitive yaptığı için sorgularda Lower fonksiyonu kullanılmasına ihtiyaç duyulmamasıdır. Bir veri tipi olarak kullanıldığı için sorgu bazlı değil sütun bazlı case-insensitive uygulanmış oluyor. EFCore’da ilgili alanı citext veri tipinde kullanmak için column type olarak belirtmek gerekiyor.

modelBuilder.Entity<Blog>().Property(b => b.Name).HasColumnType("citext");

Lower fonksiyonuna ek olarak dikkat edilmesi gereken noktalar:

Sınırlı sayıda PostgreSQL text fonksiyonu Citext ile uyumlu olacak şekilde hazırlanmıştır. Bu fonksiyonlar dışında kalanlar text veri tipi ile aynı davranışı göstereceklerdir (case sensitive). İlgili fonksiyonlar ve detaylı bilgi için https://www.postgresql.org/docs/current/citext.html
Bir alanı Citext olarak tanımladıktan sonra o alan üzerinde case sensitive arama yapılamaz.

ILIKE Operatorü

PostgresSQL’e özel bir operatör olan ILIKE, LIKE ile aynı özelliklere sahiptir ancak case insensitive’dir. LIKE operatörü için tanımlı özel karakterler (% ve _) arama metninin içinde bulunmaz ise ILIKE case insensitive equality operatörü olarak kullanılabilir. Pratikte uygulanması güçtür. Kullanıcı tarafından girilen değerlerin % veya _ içermeyeceğinden emin olmak gerekir. Eşitlik kontrollerinde kullanılması doğru olmasa da case insensitive pattern matching uygulanmak istendiğinde en uygun çözüm ILIKE olacaktır. Detaylı bilgi için https://www.postgresql.org/docs/8.3/functions-matching.html

Citext, Lower ve Collation Performans Kıyaslaması

Aşağıdaki kaynakta case insensitive çözümleri performans açısından kıyaslama yer almaktadır.

Case-insensitive pattern matching in PostgreSQL - CYBERTEC

www.cybertec-postgresql.com

Özet olarak aşağıdaki tabloyu inceleyebiliriz.

Sonuç

Yukarıdaki çözümler ve performans kıyaslaması doğrultusunda şu çıkarımlarda bulunabiliriz:

Bir sorguyu case insensitive yapmak yerine bir sütunu bütünüyle case insensitive yapmak pratikte daha uygulanabilir olacaktır. Böylece analiz dokümanında bir alanın case insensitive olduğunu belirtmek o alanla ilgili tüm sorgularda case insensitive çalışılacağını gösterir. Bu açıdan citext veri tipi uygun bir çözümdür.
Tüm sütun yerine sadece belirli sorgularda case insensitive arama yapılacak ise, o sorguya özel Lower fonksiyonu kullanmak daha uygun olacaktır. Artan veri boyutu ile index gereksinimi değerlendirilmelidir.
Collation case sensitivity için olmasa bile dil için kullanılmalıdır. Veritabanı oluşturulurken bu parametre doğru verilmez ise diğer tüm çözümler beklenmeyen çıktılar üretebilir.