Çalıştırılabilir örneklerle PostgreSQL eğitimi
Sorguları sayfada çalışan ücretsiz bir PostgreSQL eğitimi: veri tipleri, CREATE TABLE, upsert, JSONB, tarihler, CTE, pencere fonksiyonları, indeksler ve VACUUM. Sorguyu düzenle ve yeniden çalıştır.
Kursa başlaBaşlangıç
- PostgreSQL vs MySQLPostgreSQL ve MySQL ikisi de ücretsiz ilişkisel veritabanları. PostgreSQL daha zengin SQL, JSONB ve transaction içinde DDL sunar; MySQL ise zaten varsayılan olduğu yerde işletmesi daha kolaydır.
- PostgreSQL vs SQL ServerPostgreSQL ücretsiz ve açık kaynak; SQL Server ise T-SQL ve Windows merkezli araçlarıyla Microsoft'un ticari veritabanı. SQL, JSON, kilitleme ve lisans açısından nasıl ayrıldıklarını burada görebilirsin.
- PostgreSQL vs MongoDBPostgreSQL satırları sabit şemalı tablolarda saklar ve jsonb sütunlarında JSON dokümanları da tutabilir; MongoDB esnek şemalı dokümanları koleksiyonlarda saklar ve sunuculara shard eder.
- Veritabanı ve Tablo Listelemepsql'de \l veritabanlarını listeler, \c birine geçer, \dt de tablolarını listeler. SQL ile pg_database, information_schema.tables ve information_schema.columns sorgulanır.
Tablolar ve tipler
- Veri TipleriNeredeyse her sütunda kullanacağın PostgreSQL veri tipleri: tam sayılar, numeric, text, boolean, timestamptz, uuid, jsonb, diziler ve enum'lar, hangisinin ne zaman seçileceğiyle birlikte.
- CREATE TABLECREATE TABLE bir tablonun sütunlarını, tiplerini ve kısıtlarını tanımlar. Identity anahtarlar, NOT NULL, DEFAULT, UNIQUE, CHECK, IF NOT EXISTS, CREATE TABLE AS ve LIKE.
- ALTER TABLEALTER TABLE var olan bir tabloyu değiştirir: sütun ekler, adını değiştirir ve siler, USING ile sütun tipini dönüştürür, varsayılan değer, NOT NULL ve kısıt ekler.
- Birincil AnahtarlarPostgreSQL'de birincil anahtar nasıl tanımlanır ve nasıl üretilir: identity sütunları, eski SERIAL ve gen_random_uuid() ile UUID'ler. Bileşik anahtarlar ve sequence sıfırlama da burada.
- Yabancı AnahtarlarYabancı anahtar, bir sütunun yalnızca başka bir tabloda var olan değerleri kabul etmesini sağlar. REFERENCES, ON DELETE CASCADE, SET NULL ve RESTRICT, var olan tabloya anahtar eklemek ve onu indekslemek.
- Enum TipleriEnum tipi, CREATE TYPE ... AS ENUM ile oluşturulan, izin verilen değerlerin sabit ve sıralı bir listesidir. Değer ekleme ve yeniden adlandırma, değerleri listeleme, sıralama ve CHECK kısıtının ne zaman daha iyi olduğu.
- Zaman Damgalarıtimestamptz zamanda bir anı saklar ve onu oturumun saat diliminde gösterir; timestamp ise saat dilimi olmadan bir duvar saati zamanı saklar. Hiçbiri yazdığın dilimi saklamaz. Neredeyse her şey için timestamptz kullan.
Sorgular
- Join'lerJoin, iki tablonun satırlarını bir koşula göre birleştirir. INNER JOIN eşleşenleri, LEFT JOIN ilk tablonun her satırını, FULL JOIN iki tarafı da tutar; CROSS JOIN her şeyi eşleştirir, LATERAL ise her satır için bir alt sorgu çalıştırır.
- DISTINCT ONSELECT DISTINCT ON (key) ... ORDER BY key, sort her grubun ilk satırını tutar; örneğin her müşterinin en son siparişini. Yalnızca PostgreSQL'de vardır.
- COALESCE, NULLIF ve CASECOALESCE(a, b, ...) NULL olmayan ilk argümanı döndürür, NULLIF(a, b) bir değeri NULL'a çevirir, CASE WHEN ise koşula göre bir değer seçer.
- LIKE, ILIKE ve RegexLIKE metni % ve _ içeren bir kalıpla karşılaştırır, ILIKE aynısını büyük küçük harf ayırmadan yapar, ~ operatörleri ise POSIX düzenli ifadeleriyle eşleşir.
- CAST ve ::CAST(value AS type) ve value::type, PostgreSQL'de bir değeri başka bir tipe dönüştürür. İkisi aynı dönüşümdür; :: PostgreSQL'in daha kısa yazımıdır.
Veri değiştirme
- Upsert (ON CONFLICT)PostgreSQL'de INSERT ... ON CONFLICT ile upsert: EXCLUDED ile DO UPDATE, DO NOTHING, koşullu güncellemeler, sayaçlar ve eklenen satırları güncellenenlerden ayırt etme.
- RETURNINGRETURNING, INSERT, UPDATE, DELETE ve MERGE komutlarının değiştirdikleri satırları geri vermesini sağlar: üretilen id'ler, yeni değerler, silinen satırlar ve MERGE'ün hangi işlemi yaptığı.
- Transaction'larPostgreSQL'de transaction'lar nasıl çalışır: BEGIN, COMMIT ve ROLLBACK, savepoint'ler, bir hatanın açık transaction'a ne yaptığı ve izolasyon seviyeleri.
JSON ve diziler
- JSONBPostgreSQL'de jsonb ile JSON saklamak ve sorgulamak: -> ve ->> operatörleri, @> ile içerme, jsonb_set ve diğer güncellemeler, dizileri satırlara çevirmek, GIN indeksleri ve json ile jsonb farkı.
- DizilerPostgreSQL dizi sütunları: nasıl tanımlanır, indekslerin 1'den başlaması, bir dizinin değer içerip içermediğini ANY, @> ve && ile kontrol etmek, unnest ve ihtiyacın olan dizi fonksiyonları.
- string_agg ve array_aggstring_agg bir grubun değerlerini tek bir metinde birleştirir, array_agg ise onları bir diziye toplar. Sıralama, DISTINCT, FILTER, NULL davranışı ve sık karşılaşılan hatalar.
Metinler ve tarihler
- Metin FonksiyonlarıPostgreSQL'de her gün kullandığın metin fonksiyonları: substring, left ve right, || ve concat, split_part, position, replace, regexp_replace, trim, lpad ve format.
- Tarih FonksiyonlarıPostgreSQL'de tarihlerle çalışmak: now() ve current_date, interval ekleyip çıkarmak, gruplama için date_trunc, extract ve age, to_char ile biçimlendirme.
- generate_seriesgenerate_series, bir başlangıç değerinden bitiş değerine kadar bir satır kümesi döndürür: sayılar, tarihler ve timestamp'ler. Raporlardaki boşlukları doldurmak, test verisi üretmek ve takvim kurmak için kullan.
İleri sorgular
- CTE (WITH)Common table expression (CTE), bir alt sorguya WITH cümlesinde ad verir; ana sorgu onu bir tablo gibi okur. WITH RECURSIVE ise CTE'nin kendine başvurmasını sağlar, böylece ağaçlarda ve graflarda gezinebilirsin.
- Pencere FonksiyonlarıPencere fonksiyonları (window functions), ilişkili satırlardan oluşan bir gruptan her satır için bir değer hesaplar ve satırları birleştirmez: sıralamalar, kümülatif toplamlar, hareketli ortalamalar ve önceki satırla karşılaştırmalar.
Performans ve bakım
- İndekslerCREATE INDEX varsayılan olarak bir B-tree indeksi oluşturur; böylece PostgreSQL tüm tabloyu okumadan satırları bulabilir. Diğer türler (GIN, GiST, BRIN, hash) JSONB, diziler, tam metin arama, aralıklar ve çok büyük tablolar içindir.
- EXPLAIN ANALYZEEXPLAIN, PostgreSQL'in bir sorgu için seçtiği planı gösterir; EXPLAIN ANALYZE ise sorguyu çalıştırır ve her adımın gerçek satır sayılarını ve sürelerini ekler. Plan ağacını ve sık görülen düğüm adlarını okumayı öğren.
- View ve Materialized ViewBir view, her okuduğunda çalışan kayıtlı bir sorgudur. Materialized view ise sorgunun sonucunu saklar ve yalnızca REFRESH MATERIALIZED VIEW çalıştırdığında değişir.
- VACUUMVACUUM, UPDATE ve DELETE'in geride bıraktığı ölü satır sürümlerini temizler, böylece yerleri yeniden kullanılabilir. Autovacuum bunu senin için çalıştırır; bu sayfa ne zaman çalıştığını, nasıl ayarlandığını ve VACUUM FULL'un kilidine ne zaman değdiğini gösterir.
Fonksiyonlar ve tetikleyiciler
- Fonksiyonlar ve ProsedürlerCREATE FUNCTION, sorguların içinde çağırdığın, SQL ya da PL/pgSQL ile yazılmış bir fonksiyon tanımlar. CREATE PROCEDURE ise CALL ile çalıştırılan ve ilerledikçe transaction'ları commit edebilen bir rutin tanımlar.
- TetikleyicilerBir tetikleyici (trigger), satırlar eklendiğinde, güncellendiğinde ya da silindiğinde bir fonksiyonu otomatik çalıştırır. PostgreSQL'de önce trigger döndüren bir fonksiyon yazarsın, sonra onu CREATE TRIGGER ile bir tabloya bağlarsın.