Kurs PostgreSQL z przykładami do uruchomienia
Darmowy kurs PostgreSQL z zapytaniami, które uruchomisz na stronie: typy danych, CREATE TABLE, upsert, JSONB, daty, CTE, funkcje okna, indeksy i VACUUM. Zmień zapytanie i uruchom je ponownie.
Zacznij kursPierwsze kroki
- PostgreSQL vs MySQLPostgreSQL i MySQL to darmowe relacyjne bazy danych. PostgreSQL ma bogatszy SQL, JSONB i transakcyjny DDL; MySQL jest prostszy w utrzymaniu tam, gdzie już jest domyślnym wyborem.
- PostgreSQL vs SQL ServerPostgreSQL jest darmowy i open source; SQL Server to komercyjna baza Microsoftu z T-SQL i narzędziami skupionymi na Windows. Oto czym różnią się ich SQL, JSON, blokady i licencje.
- PostgreSQL vs MongoDBPostgreSQL przechowuje wiersze w tabelach ze stałym schematem i może trzymać dokumenty JSON w kolumnach jsonb; MongoDB przechowuje dokumenty o elastycznym schemacie w kolekcjach i rozkłada je na serwery przez sharding.
- Lista baz danych i tabelW psql \l wypisuje bazy danych, \c przełącza na jedną z nich, a \dt wypisuje jej tabele. W SQL odpytaj pg_database, information_schema.tables i information_schema.columns.
Tabele i typy
- Typy danychTypy danych PostgreSQL, których użyjesz w prawie każdej kolumnie: liczby całkowite, numeric, text, boolean, timestamptz, uuid, jsonb, tablice i enumy, z tym, kiedy który wybrać.
- CREATE TABLECREATE TABLE określa kolumny, typy i ograniczenia tabeli. Omawia klucze identity, NOT NULL, DEFAULT, UNIQUE, CHECK, IF NOT EXISTS, CREATE TABLE AS i LIKE.
- ALTER TABLEALTER TABLE zmienia istniejącą tabelę: dodaje, przemianowuje i usuwa kolumny, zmienia typ kolumny przez USING, ustawia wartości domyślne i NOT NULL oraz dodaje ograniczenia.
- Klucze główneJak zdefiniować klucz główny w PostgreSQL i jak go generować: kolumny identity, starszy SERIAL i UUID przez gen_random_uuid(). Do tego klucze złożone i resetowanie sekwencji.
- Klucze obceKlucz obcy sprawia, że kolumna przyjmuje tylko wartości istniejące w innej tabeli. Omawia REFERENCES, ON DELETE CASCADE, SET NULL i RESTRICT, dodanie klucza do istniejącej tabeli i jego indeks.
- Typy enumTyp enum to stała, uporządkowana lista dozwolonych wartości tworzona przez CREATE TYPE ... AS ENUM. Omawia dodawanie i zmianę nazw wartości, ich listowanie, sortowanie i to, kiedy lepszy jest CHECK.
- Znaczniki czasutimestamptz przechowuje moment w czasie i pokazuje go w strefie czasowej sesji; timestamp przechowuje czas zegarowy bez strefy. Żaden nie zapisuje strefy, którą podałeś. Prawie zawsze używaj timestamptz.
Zapytania
- Złączenia (JOIN)Złączenie łączy wiersze z dwóch tabel według warunku. INNER JOIN zostawia dopasowania, LEFT JOIN każdy wiersz pierwszej tabeli, FULL JOIN obie strony, CROSS JOIN łączy wszystko ze wszystkim, a LATERAL uruchamia podzapytanie dla każdego wiersza.
- DISTINCT ONSELECT DISTINCT ON (key) ... ORDER BY key, sort zostawia pierwszy wiersz każdej grupy, na przykład najnowsze zamówienie każdego klienta. Działa tylko w PostgreSQL.
- COALESCE, NULLIF i CASECOALESCE(a, b, ...) zwraca pierwszy argument, który nie jest NULL, NULLIF(a, b) zamienia wartość na NULL, a CASE WHEN wybiera wartość według warunku.
- LIKE, ILIKE i regexLIKE porównuje tekst ze wzorcem z % i _, ILIKE robi to samo bez rozróżniania wielkości liter, a operatory ~ dopasowują wyrażenia regularne POSIX.
- CAST i ::CAST(value AS type) i value::type zamieniają w PostgreSQL wartość na inny typ. To ta sama konwersja; :: to krótszy zapis PostgreSQL.
Zmiana danych
- Upsert (ON CONFLICT)Upsert w PostgreSQL przez INSERT ... ON CONFLICT: DO UPDATE z EXCLUDED, DO NOTHING, aktualizacje warunkowe, liczniki i odróżnianie wstawionych wierszy od zaktualizowanych.
- RETURNINGRETURNING sprawia, że INSERT, UPDATE, DELETE i MERGE zwracają zmienione wiersze: wygenerowane identyfikatory, nowe wartości, usunięte wiersze i to, która akcja MERGE się wykonała.
- TransakcjeJak działają transakcje w PostgreSQL: BEGIN, COMMIT i ROLLBACK, punkty zapisu, co błąd robi z otwartą transakcją i poziomy izolacji.
JSON i tablice
- JSONBZapisywanie i odpytywanie JSON w PostgreSQL przez jsonb: operatory -> i ->>, zawieranie przez @>, jsonb_set i inne aktualizacje, zamiana tablic na wiersze, indeksy GIN oraz json vs jsonb.
- TabliceKolumny tablicowe w PostgreSQL: deklarowanie, indeksowanie od 1, sprawdzanie, czy tablica zawiera wartość, przez ANY, @> i &&, unnest i najpotrzebniejsze funkcje tablicowe.
- string_agg i array_aggstring_agg łączy wartości grupy w jeden napis, a array_agg zbiera je w tablicę. Kolejność, DISTINCT, FILTER, obsługa NULL i błędy, na które ludzie trafiają.
Napisy i daty
- Funkcje tekstoweFunkcje tekstowe PostgreSQL, których używasz na co dzień: substring, left i right, || i concat, split_part, position, replace, regexp_replace, trim, lpad i format.
- Funkcje datPraca z datami w PostgreSQL: now() i current_date, dodawanie i odejmowanie interwałów, date_trunc do grupowania, extract i age oraz formatowanie przez to_char.
- generate_seriesgenerate_series zwraca zbiór wierszy od wartości początkowej do końcowej: liczby, daty i znaczniki czasu. Użyj jej, żeby uzupełnić luki w raportach, wygenerować dane testowe i zbudować kalendarz.
Zaawansowane zapytania
- CTE (WITH)Wyrażenie CTE nadaje nazwę podzapytaniu w klauzuli WITH, żeby główne zapytanie czytało je jak tabelę. WITH RECURSIVE pozwala mu odwołać się do siebie i przechodzić drzewa oraz grafy.
- Funkcje oknaFunkcje okna liczą wartość dla każdego wiersza z grupy powiązanych wierszy, nie łącząc ich w jeden: rankingi, sumy narastające, średnie ruchome i porównania z poprzednim wierszem.
Wydajność i konserwacja
- IndeksyCREATE INDEX domyślnie buduje indeks B-tree, żeby PostgreSQL znajdował wiersze bez czytania całej tabeli. Inne typy (GIN, GiST, BRIN, hash) obsługują JSONB, tablice, wyszukiwanie pełnotekstowe, zakresy i bardzo duże tabele.
- EXPLAIN ANALYZEEXPLAIN pokazuje plan, który PostgreSQL wybiera dla zapytania, a EXPLAIN ANALYZE wykonuje zapytanie i dodaje rzeczywiste liczby wierszy i czasy każdego kroku. Naucz się czytać drzewo planu i popularne węzły.
- Widoki i widoki zmaterializowaneWidok to zapisane zapytanie, które wykonuje się przy każdym odczycie. Widok zmaterializowany przechowuje wynik zapytania i zmienia się tylko wtedy, gdy uruchomisz REFRESH MATERIALIZED VIEW.
- VACUUMVACUUM usuwa martwe wersje wierszy, które zostawiają UPDATE i DELETE, żeby ich miejsce mogło zostać ponownie użyte. Autovacuum robi to za ciebie; ta strona pokazuje kiedy, jak go dostroić i kiedy VACUUM FULL jest wart swojej blokady.
Funkcje i wyzwalacze
- Funkcje i proceduryCREATE FUNCTION definiuje funkcję wywoływaną w zapytaniach, napisaną w SQL albo PL/pgSQL. CREATE PROCEDURE definiuje procedurę uruchamianą przez CALL, która może po drodze zatwierdzać transakcje.
- WyzwalaczeWyzwalacz automatycznie uruchamia funkcję, gdy wiersze są wstawiane, aktualizowane albo usuwane. W PostgreSQL piszesz funkcję wyzwalacza zwracającą trigger, a potem podpinasz ją do tabeli przez CREATE TRIGGER.