PostgreSQL Collation, Locale ve Türkçe Sıralama (ICU)

Üç kavram: encoding, locale, collation

Encoding baytların hangi karakterlere karşılık geldiğini belirler. initdb kümenin varsayılan kodlamasını -E veya --encoding ile seçer; seçenek verilmezse yerel ayardan çıkarmaya çalışır. Türkçe metin için pratikte UTF8 kullanılır. Sunucu ile istemci farklı kodlama kullanıyorsa PostgreSQL dönüşümü kendisi yapar; istemci tarafı client_encoding ile, psql'de \encoding ile ya da PGCLIENTENCODING ortam değişkeniyle belirlenir. Kılavuz, SQL_ASCII kodlamasının verinin belirli bir kodlamada olmasını hiç denetlemediğini ve yerel ayarı C olmayan bir veritabanında bu kombinasyonun kullanımdan kaldırılmış (deprecated) sayıldığını belirtir.

Locale dile ve bölgeye bağlı davranışların toplamıdır ve alt kategorilere ayrılır: LC_COLLATE metin sıralamasını, LC_CTYPE karakter sınıflandırmasını (hangisi harf, büyük karşılığı ne), LC_MESSAGES mesaj dilini, LC_MONETARY, LC_NUMERIC ve LC_TIME ise biçimlendirmeyi yönetir. Kılavuzun önemli kuralı şudur: LC_COLLATE ve LC_CTYPE veritabanı oluşturulurken sabitlenir ve sonradan değiştirilemez, çünkü metin sütunlarındaki indekslerin sırası bunlara bağlıdır. Diğer kategoriler sunucu parametresi olarak her an değiştirilebilir.

Collation ise bir yerel ayarı SQL içinde adıyla kullanmanızı sağlayan şema nesnesidir. Veritabanının yerel ayarı değiştirilemese de collation sayesinde tek bir sütuna, hatta tek bir ifadeye farklı sıralama kuralı verebilirsiniz. Kılavuz, yerel ayarın seçilebildiği katmanları sırayla sayar: işletim sistemi ortamı, initdb seçenekleri, CREATE DATABASE veya createdb, tablo sütunu ve tek sorgu. Her katman bir öncekinin varsayılanını daha ince düzeyde ezer.

Kendi veritabanınızın ayarlarını pg_database kataloğundan okuyabilirsiniz. Test veritabanımızda sağlayıcı b (builtin), yerel ayar C.UTF-8 çıktı:

SELECT datname, pg_encoding_to_char(encoding) AS encoding, datlocprovider,
       datlocale, datcollate, datctype
FROM pg_database WHERE datname = 'ornek_col';

  datname  | encoding | datlocprovider | datlocale | datcollate | datctype 
-----------+----------+----------------+-----------+------------+----------
 ornek_col | UTF8     | b              | C.UTF-8   | C          | C
(1 row)

Bazı kaynaklarda görülen SHOW lc_collate komutu PostgreSQL 18.1 kümemizde çalışmadı ve ERROR: unrecognized configuration parameter "lc_collate" döndürdü. Bu bilgiyi yukarıdaki gibi pg_database üzerinden ya da psql'de \l ile görün.

Üç collation sağlayıcısı: builtin, icu, libc

Sağlayıcı, sıralama ve karakter sınıflandırma davranışını hangi kütüphanenin tanımladığını belirler. Sağlayıcılar farklı katmanlarda karıştırılabilir; örneğin küme libc olabilir, tek bir veritabanı ICU kullanabilir, o veritabanındaki collation nesneleri de iki sağlayıcıdan birine bağlı olabilir. Hangi sağlayıcıyı seçerseniz seçin mesajlar gibi bazı davranışlar yine işletim sisteminden gelir.

Builtin sağlayıcının C.UTF-8 yerel ayarı Unicode kod noktalarına göre sıralar ve harf dönüşümünde "basit" eşlemeyi kullanır. PostgreSQL 18 ile gelen PG_UNICODE_FAST da kod noktası sırasıyla sıralar ama "tam" harf eşlemesini kullanır. Sürüm notları bu yerel ayarın harf dönüşümünü desteklediğini ama doğal dil sırasıyla değil kod noktası sırasıyla sıraladığını açıkça yazar. Yani builtin sağlayıcının hiçbir seçeneği Türkçe alfabe sırasını vermez.

ICU tarafında initdb, ICU'nun tanıdığı yerel ayarlardan bir küme seçip pg_collation kataloğuna -x-icu ekiyle kaydeder. Kümemizde ilgili satırlar şöyleydi; collprovider sütununda b builtin, i ICU, c libc, d veritabanının varsayılanı anlamına gelir:

SELECT collname, collprovider, colllocale, collisdeterministic
FROM pg_collation
WHERE collname IN ('tr-TR-x-icu','tr-x-icu','und-x-icu','C','ucs_basic',
                   'pg_c_utf8','pg_unicode_fast','unicode','default')
ORDER BY collname;

    collname     | collprovider |   colllocale    | collisdeterministic 
-----------------+--------------+-----------------+---------------------
 C               | c            |                 | t
 default         | d            |                 | t
 pg_c_utf8       | b            | C.UTF-8         | t
 pg_unicode_fast | b            | PG_UNICODE_FAST | t
 tr-TR-x-icu     | i            | tr-TR           | t
 tr-x-icu        | i            | tr              | t
 ucs_basic       | b            | C               | t
 und-x-icu       | i            | und             | t
 unicode         | i            | und             | t
(9 rows)

Kılavuz unicode collation'ının Unicode Collation Algorithm ile sıraladığını ve ICU kök yerel ayarı und-x-icu ile aynı davrandığını söyler. Bu dilden bağımsız makul bir sıra verir ama Türkçe kurallarını içermez; aşağıda farkı göreceksiniz. Mevcut collation'ları psql'de \dOS+ ile de listeleyebilirsiniz.

Türkçe I/i sorunu: upper, lower ve initcap

Türkçede noktasız ı'nın büyüğü I, noktalı i'nin büyüğü İ'dir. Kod noktası tabanlı ve dilden bağımsız kurallar bu ayrımı bilmez. Kılavuza göre upper, lower ve initcap ifadenin collation'ını kullanır; bu yüzden aynı fonksiyon farklı collation ile farklı sonuç verir:

SELECT upper('ıi') AS c_utf8, upper('ıi' COLLATE "tr-TR-x-icu") AS icu_tr;

 c_utf8 | icu_tr 
--------+--------
 II     | Iİ
(1 row)

SELECT lower('IİŞĞ') AS c_utf8,
       lower('IİŞĞ' COLLATE "tr-TR-x-icu") AS icu_tr,
       lower('IİŞĞ' COLLATE "und-x-icu") AS icu_und;

 c_utf8 | icu_tr | icu_und 
--------+--------+---------
 iişğ   | ıişğ   | ii̇şğ
(1 row)

Üç sütun üç farklı davranış gösteriyor. C.UTF-8 büyük I'yı i yaptı, Türkçe açısından yanlış. tr-TR-x-icu doğru sonucu verdi: I ı oldu, İ i oldu. Dilden bağımsız und-x-icu ise İ harfini "i ve birleşen nokta" olmak üzere iki kod noktasına çevirdi; ekranda i gibi görünen ama 'i' ile eşit olmayan bir metin oluştu. Arama ve karşılaştırma yapan kodda bu fark sessiz hatalara yol açar. PostgreSQL 18 ile gelen casefold() fonksiyonu büyük/küçük harf duyarsız eşleşme için tasarlanmıştır ve yine collation'a göre çalışır; kılavuza göre libc sağlayıcısında lower ile aynı davranır:

SELECT casefold('IŞIK' COLLATE "tr-TR-x-icu") AS tr, casefold('IŞIK') AS c_utf8;

  tr  | c_utf8 
------+--------
 ışık | işik
(1 row)

Türkçe alfabe sırası: ORDER BY ve COLLATE

İl adlarını üç farklı collation ile sıralayalım. C.UTF-8 kod noktası sırası kullandığı için ASCII dışı harfle başlayan iller sona gider; "Aydın" da "Ağrı"dan önce gelir, çünkü ğ harfinin kod noktası y'den büyüktür:

CREATE TABLE il (ad text);
INSERT INTO il VALUES ('Çorum'), ('Bursa'), ('Şanlıurfa'), ('Sinop'), ('Isparta'),
  ('Iğdır'), ('İzmir'), ('Ordu'), ('Uşak'), ('Zonguldak'), ('Giresun'), ('Ağrı'), ('Aydın');

SELECT string_agg(ad, ', ' ORDER BY ad) AS c_utf8_sira FROM il;
SELECT string_agg(ad, ', ' ORDER BY ad COLLATE "und-x-icu") AS und_sira FROM il;
SELECT string_agg(ad, ', ' ORDER BY ad COLLATE "tr-TR-x-icu") AS tr_sira FROM il;

                                            c_utf8_sira                                             
----------------------------------------------------------------------------------------------------
 Aydın, Ağrı, Bursa, Giresun, Isparta, Iğdır, Ordu, Sinop, Uşak, Zonguldak, Çorum, İzmir, Şanlıurfa
(1 row)

                                              und_sira                                              
----------------------------------------------------------------------------------------------------
 Ağrı, Aydın, Bursa, Çorum, Giresun, Iğdır, Isparta, İzmir, Ordu, Şanlıurfa, Sinop, Uşak, Zonguldak
(1 row)

                                              tr_sira                                               
----------------------------------------------------------------------------------------------------
 Ağrı, Aydın, Bursa, Çorum, Giresun, Iğdır, Isparta, İzmir, Ordu, Sinop, Şanlıurfa, Uşak, Zonguldak
(1 row)

und-x-icu neredeyse doğru görünür ama "Şanlıurfa"yı "Sinop"tan önce koydu: kök yerel ayar ş harfini s'nin bir varyantı gibi ele alır. Yalnızca tr-TR-x-icu Türk alfabesindeki "s, ş" sırasını uyguladı. Bu yüzden "unicode collation zaten her dili doğru sıralar" varsayımı Türkçe için geçerli değildir.

Sütun düzeyinde collation ve türetme kuralları

Bir sütuna collation verdiğinizde o sütunu kullanan her sıralama ve karşılaştırma bu kuralı devralır. Kılavuz bunu "collation derivation" olarak anlatır: COLLATE yan tümcesi açık (explicit) türetme, sütun tanımından gelen ise örtük (implicit) türetmedir. Açık olan her zaman kazanır. İki farklı örtük collation karşılaşırsa sonuç belirsizdir ve collation bilmesi gereken bir işlem hata verir:

CREATE TABLE musteri (
  id  serial PRIMARY KEY,
  ad  text COLLATE "tr-TR-x-icu",
  kod text COLLATE "C"
);
INSERT INTO musteri (ad, kod) VALUES ('ılgaz', 'a1'), ('Irmak', 'b2'), ('İpek', 'c3'), ('ipek', 'd4');

SELECT ad, upper(ad), lower(ad) FROM musteri;

  ad   | upper | lower 
-------+-------+-------
 ılgaz | ILGAZ | ılgaz
 Irmak | IRMAK | ırmak
 İpek  | İPEK  | ipek
 ipek  | İPEK  | ipek
(4 rows)

SELECT ad FROM musteri WHERE ad < kod;

ERROR:  could not determine which collation to use for string comparison
HINT:  Use the COLLATE clause to set the collation explicitly.

Çözüm, ifadelerden birine açık collation vermektir: WHERE ad < kod COLLATE "C" yazınca sorgu bayt sırasına göre çalıştı ve tek satır (Irmak) döndürdü. Kılavuz, || gibi collation umursamayan operatörlerin bu durumda hata vermediğini, ama birleştirilmiş sonucu ORDER BY ile sıralamaya kalkınca hatanın yeniden ortaya çıktığını hatırlatır. Ayrıca PostgreSQL özdeş davranan iki farklı collation nesnesini bile uyumsuz sayar; kılavuzdaki örneğe göre a COLLATE "C" < b COLLATE "POSIX" hata verir.

İndeks de collation taşır. Sütunun kendi collation'ından farklı sıralanan sorgular için ayrı indeks kurabilirsiniz: CREATE INDEX urun_ad_idx ON urun (ad COLLATE "tr-TR-x-icu");. psql'deki \d urun çıktısı indeksi btree (ad COLLATE "tr-TR-x-icu") olarak gösterdi. Kılavuz, C dışı yerel ayarların karakter işlemeyi yavaşlattığını ve sıradan indekslerin LIKE için kullanılmasını engellediğini belirtir; çözüm olarak özel operatör sınıflarını veya C collation ile kurulmuş indeksleri önerir. İndeks türleri için indeks türleri yazısına bakın.

Veritabanını Türkçe ICU ile oluşturmak

Tüm metin sütunlarının Türkçe kurala uymasını istiyorsanız veritabanını baştan ICU ile oluşturun. Buradaki tuzak şablondur: template1 builtin sağlayıcıyla oluşturulduysa farklı sağlayıcı isteyen komut reddedilir. Kılavuz, template0 dışında bir şablon kopyalanırken kodlama ve yerel ayarın değiştirilemeyeceğini, çünkü verinin bozulabileceğini açıklar:

CREATE DATABASE ornek_col_tr LOCALE_PROVIDER icu ICU_LOCALE 'tr-TR' LOCALE 'C';

ERROR:  new locale provider (icu) does not match locale provider of the template database (builtin)
HINT:  Use the same locale provider as in the template database, or use template0 as template.

CREATE DATABASE ornek_col_tr TEMPLATE template0
  LOCALE_PROVIDER icu ICU_LOCALE 'tr-TR' LOCALE 'C';
\c ornek_col_tr
SELECT upper('ıi') AS sonuc, 'Iğdır' < 'İzmir' AS kiyas;

 sonuc | kiyas 
-------+-------
 Iİ    | t
(1 row)

Bu veritabanında COLLATE yazmadan da Türkçe sonuç alındı. Kılavuzdaki initdb --locale-provider=icu --icu-locale=en örneğinde olduğu gibi aynı seçim küme düzeyinde de yapılabilir. Veritabanı oluşturma ve şablonların ayrıntıları veritabanı ve şema oluşturma, initdb seçenekleri PostgreSQL kurulumu yazısında.

ICU yerel ayar adları BCP 47 dil etiketidir (tr-TR, de-AT). Kılavuza göre libc tarzı adlar verilirse PostgreSQL bunları etikete çevirir ve bir NOTICE ile bildirir; tanınmayan adlar ise yalnızca uyarı üretir ve collation yine oluşturulur. Kümemizde şu iki komutun çıktısı bu davranışı gösterdi:

CREATE COLLATION x (provider = icu, locale = 'tr_TR.utf8');
CREATE COLLATION y (provider = icu, locale = 'turkce');

NOTICE:  using standard form "tr-TR" for ICU locale "tr_TR.utf8"
CREATE COLLATION
WARNING:  ICU locale "turkce" has unknown language "turkce"
HINT:  To disable ICU locale validation, set the parameter "icu_validation_level" to "disabled".
CREATE COLLATION

İkinci durumda collation oluştu ama beklediğiniz gibi davranmaz. Kılavuza göre icu_validation_level parametresi ERROR yapılmadıkça collation yine oluşturulur; bu yüzden uyarıları dikkate alın ve kanonik dil etiketini doğrudan yazın.

Nondeterministic collation: büyük/küçük harf duyarsız benzersizlik

Varsayılan olarak tüm collation'lar deterministiktir: iki metin ancak bayt bayt aynıysa eşittir. deterministic = false ile oluşturulan bir ICU collation ise farklı baytları eşit sayabilir. Kılavuzdaki ICU karşılaştırma düzeyleri tablosuna göre level2 aksanları ayırır ama büyük/küçük harfi ayırmaz; dil etiketine -u-ks-level2 eklemek bunu sağlar. Türkçe kurallı böyle bir collation ile e-posta sütununda benzersizlik sağlayalım:

CREATE COLLATION tr_buyukkucuk (provider = icu, locale = 'tr-TR-u-ks-level2', deterministic = false);
CREATE TABLE uye (eposta text COLLATE tr_buyukkucuk UNIQUE);
INSERT INTO uye VALUES ('Ali@ornek.com');
INSERT INTO uye VALUES ('ali@ORNEK.com');

ERROR:  duplicate key value violates unique constraint "uye_eposta_key"
DETAIL:  Key (eposta)=(ali@ORNEK.com) already exists.

Türkçe kuralın sonucu dikkat ister: bu collation ile 'Ali' = 'ALİ' doğru, 'Ali' = 'ALI' yanlış çıktı; aynı nedenle eposta LIKE 'ALI%' sıfır satır, LIKE 'ALİ%' ise bir satır döndürdü. Türkçe kural ASCII adreslerde bile I ile ı'yı ayırdığı için, yalnızca ASCII içeren e-posta gibi alanlarda kılavuzdaki und-u-ks-level2 örneği gibi dilden bağımsız bir collation daha öngörülebilir olabilir. Hangi kuralın istendiği veriye bağlıdır.

Collation sürümü ve yükseltmeler

Sıralama kuralı değişirse o kurala göre sıralanmış indeksler bozulabilir. ICU kütüphanesi doğal dildeki değişiklikleri izleyerek güncellenir; kılavuz ICU sonuçlarının kütüphane sürümüne bağlı olabileceğini açıkça yazar. PostgreSQL her collation için sürüm bilgisini kataloğa kaydeder; pg_collation_actual_version() kütüphanenin şu anki sürümünü verir:

SELECT collname, collversion, pg_collation_actual_version(oid)
FROM pg_collation WHERE collname = 'tr-TR-x-icu';

  collname   | collversion | pg_collation_actual_version 
-------------+-------------+-----------------------------
 tr-TR-x-icu | 153.128.47  | 153.128.47
(1 row)

İki değer farklıysa PostgreSQL uyarı verir. ALTER COLLATION sayfasına göre doğru sıra şudur: önce o collation'a bağlı nesneleri, örneğin REINDEX ile yeniden oluşturun, sonra ALTER COLLATION ... REFRESH VERSION ile kayıtlı sürümü güncelleyin. Veritabanının varsayılan collation'ı için karşılığı ALTER DATABASE ... REFRESH COLLATION VERSION'dır. Komut yalnızca kataloğu günceller, nesnelerin gerçekten yeniden kurulup kurulmadığını denetlemez. Sayfa, işletim sisteminin ana sürüm yükseltmesini ve daha yeni bir ICU sürümüne bağlı ikili dosyalara pg_upgrade ile geçişi bu durumun meşru nedenleri olarak sayar; ayrıntı pg_upgrade ile sürüm yükseltme yazısında.

Sık yapılan hatalar

• Veritabanının yerel ayarını sonradan değiştirmeye çalışmak: LC_COLLATE ve LC_CTYPE oluşturma anında sabitlenir; tek yol yeni bir veritabanı oluşturup veriyi taşımaktır.
• Farklı sağlayıcıyla veritabanı oluştururken TEMPLATE template0 yazmamak.
• unicode veya und-x-icu collation'ının Türkçeyi doğru sıraladığını varsaymak: ş ve s örneğinde gördüğünüz gibi Türkçe kural yalnızca Türkçe ICU collation'ıyla gelir.
• Türkçe metinde lower(ad) = '...' ile arama yapıp collation'ı düşünmemek: C.UTF-8'de "IŞIK" küçültülünce "işik" olur.
• Hata mesajı metnini ayrıştırmak: kılavuz, mesaj dili sunucunun ortamına göre değişebileceği için hata kodlarının kullanılmasını önerir; bkz. sık hatalar ve SQLSTATE kodları.

Sık Sorulan Sorular

Var olan bir veritabanını Türkçe collation'a çevirebilir miyim?

Veritabanının varsayılan yerel ayarını değiştiremezsiniz. Seçenekleriniz: önemli sütunlara ALTER TABLE ... ALTER COLUMN ad TYPE text COLLATE "tr-TR-x-icu" ile sütun collation'ı vermek ya da yeni bir ICU veritabanı oluşturup veriyi pg_dump ve pg_restore ile aktarmak.

tr-TR-x-icu ile tr-x-icu arasında fark var mı?

İkisi de kümemizde mevcuttu ve ICU yerel ayarları sırasıyla tr-TR ile tr idi. Kılavuz, Almanca örneğinde bazı bölge varyantlarının yazıldığı tarihte dil varyantıyla eşdeğer olduğunu (de-DE-x-icu ve de-x-icu) belirtir; Türkçe için aynısını varsaymak yerine birini seçip her yerde onu kullanın, çünkü PostgreSQL farklı adlı collation nesnelerini aynı davransalar bile uyumsuz sayar.

İstemcide Türkçe karakterler bozuk görünüyorsa sorun collation mı?

Genellikle hayır; bu kodlama sorunudur. SHOW client_encoding; ile istemci kodlamasını kontrol edin ve gerekirse SET client_encoding TO 'UTF8'; veya PGCLIENTENCODING ile düzeltin. Collation sıralamayı ve harf dönüşümünü etkiler, baytların çözülmesini değil.

Full text search de collation'dan etkilenir mi?

Evet, kelimeleri küçültme adımı veritabanının yerel ayarına bağlıdır; aynı Türkçe metin builtin ve ICU veritabanlarında farklı lexeme'ler üretebilir. Örnekleri full text search yazısında bulabilirsiniz.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: PostgreSQL öğrenme rehberi · String fonksiyonları ve LIKE · Veritabanı ve şema oluşturma · Full text search · İndeks türleri · MySQL ve PostgreSQL karşılaştırması