PostgreSQL String, Regex ve Biçimlendirme Function'ları: concat_ws, format(), split_part, regexp_replace, to_char ve ICU Collation

Sürüm notu: Bu yazıdaki örnekler PostgreSQL 18.7 (resmî postgres:18 Docker image'ı, ICU (International Components for Unicode; dil kurallarını içeren Unicode kütüphanesi) destekli) üzerinde, 02'de kurduğumuz Bookstore lab'ında, şema yeniden yüklendikten hemen sonra çalıştırılmıştır.

Önce Tahmin Et

Sitedeki serilerin ortak örnek uygulaması Bookstore (bir online kitapçı), kitap kartında başlığı, yazarı ve varsa çevirmeni tek satırda gösteriyor. Çevirmen kolonu çoğu kitapta boş; aşağıda onu NULL ile temsil ettik. Aynı satırı üç farklı yolla birleştirdik, sonra bir Türkçe kitap adının uzunluğuna baktık:

SELECT b.title || ' / ' || a.name || ' / ' || NULL AS pipe,
       concat(b.title, ' / ', a.name, ' / ', NULL) AS concat_result,
       concat_ws(' / ', b.title, a.name, NULL, b.published_year) AS concat_ws_result
FROM books AS b
JOIN authors AS a ON a.author_id = b.author_id
WHERE b.book_id = 3;
SELECT length('Çalıkuşu') AS len, octet_length('Çalıkuşu') AS bytes,
       length('Dune '::char(10)) AS char_len, octet_length('Dune '::char(10)) AS char_bytes;

Üç kolondan hangisi boş gelir? "Çalıkuşu" kaç karakter, kaç byte? char(10)'a yazılmış "Dune" için length ne der?

 pipe |           concat_result            |            concat_ws_result
------+------------------------------------+----------------------------------------
      | Middlemarch #3 / Guido Dijkstra /  | Middlemarch #3 / Guido Dijkstra / 1934

 len | bytes | char_len | char_bytes
-----+-------+----------+------------
   8 |    11 |        4 |         10

|| standart SQL operatörü ve bir tarafı NULL ise sonuç da NULL; satırın tamamı kayboldu. concat NULL'ı boş metin sayıyor ama ayırıcıyı bırakıyor, sondaki " / " ondan. concat_ws ise NULL argümanı ayırıcısıyla birlikte atlıyor ve sayıyı kendisi metne çeviriyor. Uzunlukta da iki ayrı soru var: length karakter sayıyor (8), octet_length byte sayıyor (UTF-8 kodlamasında ç, ı ve ş ikişer byte, toplam 11). char(n)'de length sondaki boşlukları saymıyor ama disk 10 byte. Bu yazıda PostgreSQL'in günlük işte en çok kullanılan string, regex ve biçimlendirme function'larını Bookstore verisi üzerinde görüyoruz. Aynı işlerin T-SQL karşılıkları SQL Temelleri serisinin "T-SQL String Function'ları" yazısında; burada T-SQL'i tekrar etmiyoruz, yalnızca farkın önemli olduğu yerde anıyoruz.

Günlük Function'lar: Parçalamak, Temizlemek, Doldurmak

Parçalamak: split_part, substring, strpos, left/right

Bookstore'un sipariş numarası BK-00001042 biçiminde. İçindeki sayıyı almak, ayırıcının yerini bulmak ve uçlardan kesmek için:

SELECT order_number,
       split_part(order_number, '-', 2) AS digits,
       ltrim(split_part(order_number, '-', 2), '0')::int AS seq,
       split_part('2026-11-15', '-', -1) AS last_part,
       strpos(order_number, '-') AS dash_at,
       left(order_number, 2) AS prefix,
       right(order_number, 4) AS tail
FROM orders
WHERE order_id = 1042;
SELECT title,
       substring(title FROM '#(\d+)$') AS copy_no,
       substring(title, 1, strpos(title, ' #') - 1) AS base_title,
       overlay(title PLACING 'Dune Messiah' FROM 1 FOR 4) AS overlaid
FROM books
WHERE book_id = 20000;

 order_number |  digits  | seq  | last_part | dash_at | prefix | tail
--------------+----------+------+-----------+---------+--------+------
 BK-00001042  | 00001042 | 1042 | 15        |       3 | BK     | 1042

    title    | copy_no | base_title |      overlaid
-------------+---------+------------+---------------------
 Dune #20000 | 20000   | Dune       | Dune Messiah #20000

split_part ayırıcıya göre n'inci parçayı veriyor; PostgreSQL 14'ten beri negatif n sondan sayıyor. SQL Server'da bunun doğrudan karşılığı yok, STRING_SPLIT'in ordinal kolonu ya da CHARINDEX hesabı gerekiyor. strpos ve standart yazımı position('-' IN order_number) 1 tabanlı konum döndürüyor, bulamazsa 0. substring'in iki yüzü var: sayılarla klasik kesme, FROM ile bir regex verildiğinde ise eşleşen kısım ya da ilk parantez grubu. Kitap başlığındaki kopya numarasını tek ifadeyle almak böyle. overlay, T-SQL'deki STUFF'ın standart karşılığı: belli bir konumdan belli sayıda karakteri başka bir metinle değiştiriyor.

Temizlemek ve Doldurmak: trim, lpad, initcap, translate

SELECT btrim('  --Dune--  ', ' -') AS btrimmed, trim(BOTH '0' FROM '000420') AS trimmed,
       lpad('42', 8, '0') AS padded, rpad('Dune', 8, '.') || '|' AS rpadded, lpad('Middlemarch', 6) AS lpad_cut;
SELECT initcap('the hobbit, or there and back again') AS initcapped,
       translate('Moby-Dick #8', '-#', ' ') AS translated, repeat('*', 5) AS stars;
SELECT string_to_array('classic,bestseller,,gift', ',') AS arr,
       array_to_string(ARRAY['a', NULL, 'c'], ',', '?') AS joined;

 btrimmed | trimmed |  padded  |  rpadded  | lpad_cut
----------+---------+----------+-----------+----------
 Dune     | 42      | 00000042 | Dune....| | Middle

             initcapped              | translated  | stars
-------------------------------------+-------------+-------
 The Hobbit, Or There And Back Again | Moby Dick 8 | *****

             arr              | joined
------------------------------+--------
 {classic,bestseller,"",gift} | a,?,c

Burada üç küçük sürpriz var. trim yalnızca boşluk değil, verdiğin her karakteri iki uçtan siliyor: '000420''nin sonundaki sıfır da gitti ve 420 yerine 42 kaldı. Baştaki sıfırları atmak için yukarıdaki gibi ltrim kullan. lpad hedef uzunluktan uzun metni sessizce kesiyor: "Middlemarch" "Middle" oldu. translate karakter karakter eşliyor ve karşılığı olmayan karakteri siliyor: - boşluğa döndü, # ise karşılık listesinde olmadığı için kayboldu. initcap her kelimenin ilk harfini büyütüyor ama İngilizce başlık kuralını bilmiyor ("Or", "And"). Ayırıcıyla bölmek için string_to_array ve satır döndüren string_to_table var; ikisi de iki ayırıcı arasındaki boş parçayı boş metin olarak koruyor.

T-SQL'den Gelenler İçin Kısa Eşleme

format(): Güvenli Dinamik Metin

format, C'deki printf'e benzeyen bir function ama SQL için üç belirleyicisi var: %s değeri düz metin olarak, %I identifier (tablo, kolon adı) olarak, %L literal (tırnaklı değer) olarak yerleştiriyor.

SELECT format('Order %s for customer %s: %s %s', order_number, customer_id, total_amount, currency) AS line
FROM orders
WHERE order_id = 1042;
SELECT format('SELECT * FROM %I WHERE title = %L', 'Books', 'The Hitchhiker''s Guide') AS safe_sql,
       format('%L', NULL) AS null_literal, format('%s', NULL) AS null_s,
       format('%2$s, %1$s', 'Ada', 'Lovelace') AS positional,
       format('[%-10s][%10s]', 'Dune', 'Emma') AS widths;

                      line
-------------------------------------------------
 Order BK-00001042 for customer 1328: 101.81 TRY

                           safe_sql                            | null_literal | null_s |  positional   |          widths
---------------------------------------------------------------+--------------+--------+---------------+--------------------------
 SELECT * FROM "Books" WHERE title = 'The Hitchhiker''s Guide' | NULL         |        | Lovelace, Ada | [Dune      ][      Emma]

%I büyük harfli "Books"u çift tırnağa aldı (02'deki tırnaklı tablo adı tuzağını hatırla), %L kesme işaretini iki katına çıkardı ve NULL'ı tırnaksız NULL olarak yazdı. %s ise NULL'ı boş metne çeviriyor. Argümanlara %2$s gibi sırayla ve genişlik vererek de başvurulabiliyor. Aynı işi tek değer için quote_ident, quote_literal ve quote_nullable yapıyor.

Bunun neden önemli olduğunu bir DO bloğuyla (adsız, bir kez çalışan kod bloğu; dili (PL/pgSQL, PostgreSQL'in kendi procedural dili) 22. yazıda ayrıntılı) gördük. Kullanıcıdan gelen başlığı string birleştirerek sorguya koyan dinamik SQL:

DO $$
DECLARE
    v_title text := 'Dune''; DROP TABLE books; --';
    v_count bigint;
BEGIN
    EXECUTE 'SELECT count(*) FROM books WHERE title = ''' || v_title || '''' INTO v_count;
END
$$;

ERROR:  cannot drop table books because other objects depend on it
DETAIL:  constraint fk_order_items_books on table order_items depends on table books
constraint fk_stock_items_books on table stock_items depends on table books
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
CONTEXT:  SQL statement "SELECT count(*) FROM books WHERE title = 'Dune'; DROP TABLE books; --'"
PL/pgSQL function inline_code_block line 6 at EXECUTE

Hata mesajı rahatlatıcı değil: DROP TABLE books gerçekten çalıştı ve tabloyu yalnızca ona bağlı foreign key'ler kurtardı. Bağımlılığı olmayan bir tablo olsaydı silinmişti. Aynı sorgu format ile ya da değeri parametre olarak geçen USING ile:

DO $$
DECLARE
    v_title text := 'Dune''; DROP TABLE books; --';
    v_count bigint;
BEGIN
    EXECUTE format('SELECT count(*) FROM %I WHERE title = %L', 'books', v_title) INTO v_count;
    RAISE NOTICE 'format: % rows', v_count;
    EXECUTE 'SELECT count(*) FROM books WHERE title = $1' INTO v_count USING v_title;
    RAISE NOTICE 'USING: % rows', v_count;
END
$$;

NOTICE:  format: 0 rows
NOTICE:  USING: 0 rows

İkisi de değeri tek bir metin olarak aradı ve sıfır satır buldu. Kural basit: değer için önce USING (parametre), parametre olamayan yerde (tablo ya da kolon adı) %I, değeri metnin içine gömmek zorundaysan %L. Uygulama tarafında aynı kural Npgsql parametreleriyle geçerli; 11. yazıda gördük. Web Güvenliği serisinin injection yazısı da konunun genel hâlini anlatıyor.

LIKE, ILIKE, SIMILAR TO ve POSIX Regex

PostgreSQL'de metin eşleştirmenin dört yolu var. Aynı tabloda yan yana saydık:

SELECT count(*) FILTER (WHERE title LIKE 'Dune%') AS like_cnt,
       count(*) FILTER (WHERE title ILIKE 'dune%') AS ilike_cnt,
       count(*) FILTER (WHERE title ~ '^Dune #\d{1,2}$') AS regex_cnt,
       count(*) FILTER (WHERE title ~* '^(dune|emma) ') AS regex_ci_cnt,
       count(*) FILTER (WHERE title SIMILAR TO '(Dune|Emma) #[0-9]{3}') AS similar_cnt
FROM books;

 like_cnt | ilike_cnt | regex_cnt | regex_ci_cnt | similar_cnt
----------+-----------+-----------+--------------+-------------
     2000 |      2000 |         4 |         4000 |          90

LIKE standart ve büyük/küçük harfe duyarlı; ILIKE PostgreSQL'e özgü, duyarsız hâli. SQL Server'da duyarlılık collation'dan gelir, PostgreSQL'de ise varsayılan collation'lar duyarlı olduğu için ayrı operatör kullanılır. ~ ve ~* POSIX (Portable Operating System Interface; Unix dünyasının ortak standartları) regex'leri, duyarlı ve duyarsız; !~ ile !~* değilleri. Önemli fark şurada: regex metnin herhangi bir yerinde eşleşir, o yüzden tam eşleşme için ^ ve $ gerekiyor. SIMILAR TO ise standardın, LIKE ile regex arasında kalan melez syntax'ı ve her zaman metnin tamamına eşleşiyor: üç haneli numaralı Dune ve Emma'lar 90 tane. Pratikte SIMILAR TO'ya gerek kalmıyor; ya LIKE yetiyor ya da doğrudan regex yazılıyor.

regexp_* Function'ları

SELECT regexp_match('BK-00001042', '^([A-Z]+)-(\d+)$') AS m,
       (regexp_match('BK-00001042', '^([A-Z]+)-(\d+)$'))[2]::int AS seq;
SELECT regexp_matches('ISBN 978-0-441-17271-9, ISBN 978-0-261-10221-7', '97[89][-0-9]{14}', 'g') AS found;
SELECT regexp_replace('978-0-441-17271-9', '[^0-9]', '') AS first_only,
       regexp_replace('978-0-441-17271-9', '[^0-9]', '', 'g') AS all_digits,
       regexp_replace('Hopper, Grace', '^(\w+), (\w+)$', '\2 \1') AS swapped;
SELECT regexp_count('The Lord of the Rings', 'the', 1, 'i') AS cnt,
       regexp_like('9780441172719', '^97[89]\d{10}$') AS is_isbn,
       regexp_substr('BK-00001042', '\d+') AS sub,
       regexp_instr('Moby-Dick #8', '#') AS pos;
SELECT word, n FROM regexp_split_to_table('The Time  Machine', '\s+') WITH ORDINALITY AS t(word, n);

       m       | seq
---------------+------
 {BK,00001042} | 1042

        found
---------------------
 {978-0-441-17271-9}
 {978-0-261-10221-7}

    first_only    |  all_digits   |   swapped
------------------+---------------+--------------
 9780-441-17271-9 | 9780441172719 | Grace Hopper

 cnt | is_isbn |   sub    | pos
-----+---------+----------+-----
   2 | t       | 00001042 |  11

  word   | n
---------+---
 The     | 1
 Time    | 2
 Machine | 3

regexp_match ilk eşleşmenin gruplarını bir text[] olarak döndürüyor; array index'iyle istediğin grubu alıyorsun. regexp_matches 'g' flag'iyle her eşleşme için bir satır üretiyor: metindeki iki ISBN (kitapların uluslararası standart numarası) iki satır oldu. regexp_replace'te en sık yapılan hata 'g''yi unutmak: ilk kolonda yalnızca ilk tire silindi. \2 \1 gibi back reference'larla grupların yeri değiştirilebiliyor. PostgreSQL 15 ile Oracle ve SQL Server 2025'teki adlarla aynı olan regexp_count, regexp_like, regexp_substr ve regexp_instr geldi; eski kodda aynı işlerin ~ ve substring ile yazıldığını görürsün. regexp_split_to_table regex'e göre bölüyor, iki boşluk \s+ sayesinde tek ayırıcı sayıldı; WITH ORDINALITY parça sırasını ekliyor.

Bookstore: ISBN Doğrulamak ve Slug Üretmek

ISBN-13'ün biçimi regex ile, kontrol hanesi ise aritmetikle doğrulanır: haneler sırayla 1 ve 3 ile çarpılıp toplanır, toplam 10'a tam bölünmelidir.

SELECT isbn,
       isbn ~ '^97[89][0-9]{10}$' AS format_ok,
       CASE WHEN isbn ~ '^97[89][0-9]{10}$' THEN
           (SELECT sum(substr(isbn, i, 1)::int * CASE WHEN i % 2 = 0 THEN 3 ELSE 1 END)
            FROM generate_series(1, 13) AS i) % 10 = 0
       END AS checksum_ok
FROM (VALUES ('9780441172719'), ('9780441172718'), ('978044117271X')) AS v(isbn);
SELECT count(*) AS total,
       count(*) FILTER (WHERE isbn ~ '^97[89][0-9]{10}$') AS format_ok,
       count(*) FILTER (WHERE (SELECT sum(substr(isbn, i, 1)::int * CASE WHEN i % 2 = 0 THEN 3 ELSE 1 END)
                               FROM generate_series(1, 13) AS i) % 10 = 0) AS checksum_ok
FROM books;

     isbn      | format_ok | checksum_ok
---------------+-----------+-------------
 9780441172719 | t         | t
 9780441172718 | t         | f
 978044117271X | f         |

 total | format_ok | checksum_ok
-------+-----------+-------------
 40000 |     40000 |        4000

Son hanesi değiştirilmiş numara biçim kontrolünden geçti ama kontrol hanesinden geçemedi. CASE burada süs değil: "X" içeren değerde substr(...)::int doğrudan invalid input syntax for type integer: "X" hatası veriyor, o yüzden aritmetiğe yalnızca biçimi doğru olanlar giriyor (üçüncü satırın kontrol kolonu bu yüzden boş). Lab'daki 40.000 ISBN'in hepsinin biçimi doğru ama yalnızca 4.000'inin kontrol hanesi tutuyor: şemadaki numaralar '978' || lpad(n, 10, '0') ile üretilmiş, gerçek ISBN değiller. Gerçek katalogda bu kontrol bir CHECK constraint ya da domain olmalı; 03'teki isbn13 domain'i yalnızca biçimi kontrol ediyordu, kontrol hanesini bir function'a koyup constraint'te kullanmayı 22. yazıda yapacağız.

Kitap sayfasının URL'i için başlıktan slug üretmek de bir zincir: Türkçe kurallarla küçült, Türkçe harfleri ASCII (yalnızca İngilizce alfabeyi kapsayan temel karakter seti) karşılıklarına çevir, harf ve rakam dışındaki her şeyi tireye dönüştür, uçlardaki tireleri at.

SELECT title,
       trim(BOTH '-' FROM regexp_replace(
           translate(lower(title COLLATE "tr-TR-x-icu"), 'çğıöşüâî', 'cgiosuai'),
           '[^a-z0-9]+', '-', 'g')) AS slug
FROM (VALUES ('Çalıkuşu'), ('İnce Memed'), ('Kürk Mantolu Madonna'), ('The Hobbit #2'),
             ('Moby-Dick, or The Whale')) AS v(title);

          title          |          slug
-------------------------+------------------------
 Çalıkuşu                | calikusu
 İnce Memed              | ince-memed
 Kürk Mantolu Madonna    | kurk-mantolu-madonna
 The Hobbit #2           | the-hobbit-2
 Moby-Dick, or The Whale | moby-dick-or-the-whale

Şapkalı harfleri (â, î) de listeye ekledik; bu liste Türkçe için yeterli ama Almanca ß gibi harfler gelecekse 10. yazıdaki unaccent extension'ı daha genel bir çözüm.

to_char ve to_number: Sayıyı Biçimlendirmek

SHOW lc_numeric;
SELECT to_char(1234.5, '999G999D99') AS padded, to_char(1234.5, 'FM999G999D00') AS fm,
       to_char(1234567.5, 'FM999G999D00') AS too_big, to_char(42, 'FM00000') AS zeros,
       to_char(0.256, 'FM990.0%') AS pct;

 lc_numeric
------------
 en_US.utf8

   padded    |    fm    |  too_big   | zeros | pct
-------------+----------+------------+-------+------
    1,234.50 | 1,234.50 | ###,###.## | 00042 | 0.3%

Kalıpta 9 isteğe bağlı hane, 0 zorunlu hane, G binlik ayırıcı, D ondalık ayırıcı. Başa eklenen FM dolgu boşluklarını kapatıyor; o olmadan sonuç sabit genişlikte ve başında boşluklar var, işaret için de bir yer ayrılıyor. Kalıba sığmayan sayı hata vermiyor, # ile doluyor: kalıbı en büyük değere göre yazmak gerekiyor. % ise yalnızca bir karakter; 0,256'yı yüzdeye çevirmedi, 100 ile sen çarpmalısın. G ve D ayırıcıları lc_numeric ayarından alıyor. Türkçe biçim için onu değiştirmeyi denedik:

SET lc_numeric = 'tr_TR.UTF-8';
SELECT translate(to_char(1234.5, 'FM999G999G990D00'), ',.', '.,') || ' TL' AS tr_price;
SELECT to_number('1.234,50', 'FM9G999D99') AS parsed_as_en,
       to_number(translate('1.234,50', '.,', ',.'), 'FM9G999D99') AS parsed_as_tr;

ERROR:  invalid value for parameter "lc_numeric": "tr_TR.UTF-8"

  tr_price
-------------
 1.234,50 TL

 parsed_as_en | parsed_as_tr
--------------+--------------
         1.23 |       1234.5

Resmî Docker image'ında yalnızca en_US.utf8, C ve POSIX libc locale'leri var (locale -a), o yüzden lc_numeric Türkçeye çevrilemiyor. En taşınabilir çözüm, ayırıcıları translate ile yer değiştirmek. Asıl tehlike ters yönde: to_number Türkçe yazılmış "1.234,50"yi İngilizce kurallarla okuyunca hata vermedi, 1,23 döndürdü. Kullanıcı girdisini veritabanında parse etmek yerine uygulamada CultureInfo ile parse edip parametre olarak göndermek daha güvenli. Bookstore'un aylık TL cirosu raporu bu yöntemle:

SELECT to_char(created_at, 'YYYY-MM') AS month,
       translate(to_char(sum(total_amount), 'FM999G999G990D00'), ',.', '.,') || ' TL' AS revenue
FROM orders
WHERE currency = 'TRY' AND created_at >= '2026-08-01' AND created_at < '2026-10-01'
GROUP BY 1
ORDER BY 1;

  month  |     revenue
---------+-----------------
 2026-08 | 1.203.586,29 TL
 2026-09 | 1.144.863,78 TL

Tarih tarafındaki to_char kalıplarını (YYYY-MM, TMMonth) ve bu sorgudaki ay sınırlarının saat dilimiyle ilişkisini bir sonraki yazıda ele alıyoruz.

Türkçe Harfler: Varsayılan Collation ve ICU

03'te upper ve lower'ın Türkçe i/İ davranışını ve nondeterministic collation'ı görmüştük. Aynı sorun initcap ve ILIKE'ta da var:

SELECT initcap('istanbul ırmak') AS default_initcap,
       initcap('istanbul ırmak' COLLATE "tr-TR-x-icu") AS tr_initcap,
       lower('KIRMIZI İNCİ') AS default_lower,
       lower('KIRMIZI İNCİ' COLLATE "tr-TR-x-icu") AS tr_lower;
SELECT 'IŞIK' ILIKE 'ışık' AS default_ilike, 'IŞIK' COLLATE "tr-TR-x-icu" ILIKE 'ışık' AS tr_ilike;

 default_initcap |   tr_initcap   | default_lower |   tr_lower
-----------------+----------------+---------------+--------------
 Istanbul Irmak  | İstanbul Irmak | kirmizi inci  | kırmızı inci

 default_ilike | tr_ilike
---------------+----------
 f             | t

Lab database'inin varsayılan collation'ı en_US.utf8: KIRMIZI'yı kirmizi yapıyor ve "IŞIK" ile "ışık"ı farklı sayıyor. tr-TR-x-icu ile ikisi de doğru. COLLATE'i ifadeye, kolona ya da database'e verebilirsin; kolona verdiğinde o kolonun index'i de o kurallarla sıralanıyor. Büyük/küçük harf duyarsız eşitlik için citext extension'ı da var (13. yazı), ama PostgreSQL dokümantasyonu artık nondeterministic collation'ı öneriyor; citext küçük harfe çevirirken her zaman database'in kurallarını kullanıyor, yani Türkçe I/ı sorununu çözmüyor.

Index ve LIKE: text_pattern_ops ve pg_trgm

SQL Server'dan gelen birinin beklentisi şu: title üzerinde bir index varsa LIKE 'Dune #12%' onu kullanır. PostgreSQL'de bu, collation'a bağlı:

CREATE INDEX ix_books_title ON books (title);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT book_id FROM books WHERE title = 'Dune #120';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT book_id FROM books WHERE title LIKE 'Dune #12%';

                             QUERY PLAN
---------------------------------------------------------------------
 Index Scan using ix_books_title on books (actual rows=1.00 loops=1)
   Index Cond: (title = 'Dune #120'::text)
   Index Searches: 1
   Buffers: shared hit=1 read=3
 Planning:
   Buffers: shared hit=16 read=1

                  QUERY PLAN
-----------------------------------------------
 Seq Scan on books (actual rows=56.00 loops=1)
   Filter: (title ~~ 'Dune #12%'::text)
   Rows Removed by Filter: 39944
   Buffers: shared hit=444
 Planning:
   Buffers: shared hit=5

Eşitlik index'i kullandı, önek araması kullanmadı. en_US.utf8 gibi bir collation'da sıralama dil kurallarına göre yapıldığı için B-tree'deki sıra, byte bazında "şununla başlar" aralığıyla örtüşmüyor. Çözüm, karakterleri byte sırasıyla karşılaştıran operator class'la ikinci bir index:

CREATE INDEX ix_books_title_pattern ON books (title text_pattern_ops);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT book_id FROM books WHERE title LIKE 'Dune #12%';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT book_id FROM books WHERE title ILIKE '%hobbit #12%';
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ix_books_title_trgm ON books USING gin (title gin_trgm_ops);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT book_id FROM books WHERE title ILIKE '%hobbit #12%';

                                   QUERY PLAN
--------------------------------------------------------------------------------
 Index Scan using ix_books_title_pattern on books (actual rows=56.00 loops=1)
   Index Cond: ((title ~>=~ 'Dune #12'::text) AND (title ~<~ 'Dune #13'::text))
   Filter: (title ~~ 'Dune #12%'::text)
   Index Searches: 1
   Buffers: shared hit=21 read=4
 Planning:
   Buffers: shared hit=63 read=1

                  QUERY PLAN
-----------------------------------------------
 Seq Scan on books (actual rows=56.00 loops=1)
   Filter: (title ~~* '%hobbit #12%'::text)
   Rows Removed by Filter: 39944
   Buffers: shared hit=444
 Planning:
   Buffers: shared hit=4

                                 QUERY PLAN
----------------------------------------------------------------------------
 Bitmap Heap Scan on books (actual rows=56.00 loops=1)
   Recheck Cond: (title ~~* '%hobbit #12%'::text)
   Heap Blocks: exact=14
   Buffers: shared hit=31
   ->  Bitmap Index Scan on ix_books_title_trgm (actual rows=56.00 loops=1)
         Index Cond: (title ~~* '%hobbit #12%'::text)
         Index Searches: 1
         Buffers: shared hit=17
 Planning:
   Buffers: shared hit=25

text_pattern_ops index'i LIKE'ı iki karşılaştırmaya çevirdi ve 444 yerine 25 buffer okudu. Bu index yalnızca önek aramasına yarar; ORDER BY title ve normal karşılaştırmalar için ilk index'e hâlâ ihtiyaç var. Database C collation'ıyla oluşturulmuşsa tek index ikisine de yeter. Metnin ortasında, büyük/küçük harf duyarsız arama (ILIKE '%...%') ise B-tree ile hiç hızlanmaz; pg_trgm'in GIN (Generalized Inverted Index; bir değerin parçalarını ayrı ayrı index'leyen yapı) index'i trigram'larla adayları buldu ve 31 buffer'da bitirdi. pg_trgm'in benzerlik aramasını ve maliyetini 06 ve 10. yazılarda görmüştük. Aynı index ~ ve ~* regex aramalarını da hızlandırabiliyor.

Üretimde Dikkat

Metin kolonlarında aranan her kalıp için hangi index'in kullanılacağını EXPLAIN ile kontrol et: eşitlik normal B-tree ile, önek text_pattern_ops ya da C collation ile, ortadan arama ve regex trigram GIN ile hızlanıyor. GIN index'in yazma maliyeti var; 40.000 satırlık bir başlık kolonunda sorun değil, sık güncellenen büyük bir tabloda ölç.

Collation'ı bir yerde karar olarak sabitle. Ekranda Türkçe sıralama ve initcap gerekiyorsa kolon ya da database ICU tr-TR ile; e-posta, kod ve ISBN gibi dil bağımsız kolonlarda C ya da builtin collation daha öngörülebilir. Collation sürümü değişince index'lerin yeniden oluşturulması gerektiğini 03'te gördük.

Biçimlendirmeyi mümkün olduğunca uygulamaya bırak. to_char rapor sorgularında ve psql'de pratik, ama API'nin döndürdüğü veride sayı sayı olarak kalsın; "1.234,50 TL"yi istemci kendi kültürüyle üretsin.

Sık Düşülen Tuzaklar

|| ile NULL birleştirmek: tek bir boş kolon bütün satırı NULL yapıyor. Görünen metin üreten sorgularda concat_ws ya da coalesce kullan.

String birleştirerek dinamik SQL yazmak: yukarıdaki DROP TABLE gerçekten çalıştı. Değer için USING, ad için %I.

regexp_replace'te 'g''yi unutmak ve regex'in metnin ortasında da eşleştiğini atlamak: doğrulama regex'leri ^ ile başlayıp $ ile bitmeli.

trim(BOTH '0' ...) ile baştaki sıfırları silmeye çalışmak: sondakiler de gidiyor. ltrim kullan ya da sayıya çevir.

Varsayılan collation'ı Türkçe sanmak: en_US ile "IŞIK" ve "ışık" farklı, KIRMIZI'nın küçüğü kirmizi. Türkçe kurallar yalnızca ICU tr-TR collation'ı ile geliyor.

ILIKE '%x%''i B-tree index'le hızlandırmayı beklemek: önündeki % yüzünden hiçbir B-tree kullanılamaz; trigram index ya da 10. yazıdaki full-text search gerekiyor.

Küçük Bir Deneme

★☆☆ customers tablosundaki e-postalardan (customer42@example.com) müşteri numarasını üç farklı yolla çıkar: split_part + replace, substring ... FROM ve regexp_substr. Üçünün de aynı sonucu verdiğini customer_id ile karşılaştırarak doğrula.

★★☆ Slug ifadesini books'un bir kopyasına generated column olarak ekle ve aynı slug'a sahip kitap olup olmadığını kontrol et. Sonra slug'a unique index koymayı dene: STORED yazmadığında 18'de ne oluyor? 16. yazıyı hatırlayarak önce tahmin et.

★★★ ix_books_title_trgm index'iyle title ~* '^the (hobbit|silmarillion) #1\d{3}$' sorgusunun planına bak. Sonra aynı aramayı text_pattern_ops index'iyle hızlandırabilecek şekilde iki ayrı LIKE koşuluna çevir ve buffer sayılarını karşılaştır.

Bir sonraki yazıda tarih ve saatlere geçiyoruz: now() ile clock_timestamp() farkı, date_trunc, AT TIME ZONE, interval aritmetiği ve generate_series ile boş günleri doldurmak.