Dialekt SQL w PostgreSQL: najważniejsze różnice i rozszerzenia

W tym artykule poznasz najważniejsze rozszerzenia dialektu PostgreSQL — od dodatkowych operatorów i funkcji, przez typy danych, po niestandardowe składnie i różnice wobec standardu ISO SQL. Dzięki temu zrozumiesz, kiedy Twoje zapytania są „czystym SQL-em”, a kiedy korzystają z potęgi rozszerzeń PostgreSQL.

Czym jest dialekt SQL

Dialekt SQL to zbiór rozszerzeń, zachowań i ograniczeń względem standardu ISO/IEC SQL, charakterystyczny dla danego silnika. Dialekt SQL PostgreSQL jest bliski standardowi, ale zawiera wiele praktycznych udogodnień: własne typy, operatory, funkcje i składnię ułatwiającą pracę z danymi. Świadomość różnic ułatwia ocenę przenośności zapytań oraz planowanie migracji między bazami.

PostgreSQL implementuje kluczowe elementy standardu (transakcje, widoki, CTE, okna), jednocześnie oferując unikalne cechy, jak JSONB, zakresy, DISTINCT ON, ON CONFLICT czy bogaty system operatorów. Jeśli porównujesz podejścia między silnikami, zobacz także: PostgreSQL vs MySQL: różnice, które mają znaczenie.

Rozszerzenia składniowe w PostgreSQL

Do najczęściej używanych rozszerzeń PostgreSQL należą: RETURNING w INSERT/UPDATE/DELETE, UPSERT przez INSERT ... ON CONFLICT, DISTINCT ON, LATERAL w FROM, ILIKE, SIMILAR TO oraz wsparcie dla tablic i JSONB z dedykowanymi operatorami. PostgreSQL ma również silne CTE, w tym WITH RECURSIVE, które pozwalają budować czytelne, wieloetapowe zapytania.

Przykład łączy kilka rozszerzeń: UPSERT z ON CONFLICT i natychmiastowy odczyt klucza przez RETURNING. CTE pokazuje przygotowanie danych wejściowych:

SQL
Kopiuj
1
2
3
4
5
6
7
8
WITH input AS (
  SELECT 1::int AS id, 'Ala'::text AS name
)
INSERT INTO users(id, name)
SELECT id, name FROM input
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name
RETURNING id, name;

Więcej o składni CTE znajdziesz w: Podzapytania i CTE w SQL.

Typy niestandardowe i rzutowania

Typy niestandardowe PostgreSQL to m.in. JSONB, HSTORE, zakresy (int4range, tsrange), tablice dowolnych typów, ENUM, UUID, CITEXT czy domeny. JSONB z indeksami GIN umożliwia szybkie filtrowanie po kluczach i zawartości, a zakresy pozwalają elegancko modelować przedziały z operatorami przecięcia i zawierania. Mechanizm rzutowań (::typ lub CAST) jest konsekwentny i łatwy w użyciu.

Przykład pracy z JSONB i rzutowaniami typów:

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
-- Filtr po zawartości JSONB i odczyt prostego pola
SELECT id, data->>'email' AS email
FROM customers
WHERE data @> '{"active": true}'::jsonb;

-- Tablice i operator ANY/ALL z rzutowaniem
SELECT *
FROM orders
WHERE status = ANY(ARRAY['new','paid']::text[]);

Jeśli chcesz porównać dostępne typy między silnikami, zobacz: Typy danych w SQL: porównanie PostgreSQL i MySQL.

Funkcje własne i procedury

PostgreSQL obsługuje funkcje (CREATE FUNCTION) i procedury (CREATE PROCEDURE). Funkcje mogą zwracać skalary, rekordy i zbiory (SETOF), mieć atrybuty IMMUTABLE/STABLE/VOLATILE oraz działać jako SECURITY DEFINER. Procedury (CALL) wspierają kontrolę transakcji wewnątrz ciała procedury. Najczęściej używanym językiem jest PL/pgSQL, ale dostępne są też inne języki.

Krótki przykład funkcji w PL/pgSQL z RETURNING:

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
CREATE OR REPLACE FUNCTION set_username(u_id int, new_name text)
RETURNS TABLE(id int, name text)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE users SET name = new_name WHERE id = u_id
  RETURNING users.id, users.name INTO id, name;
  RETURN NEXT;
END;
$$;

Operatory specyficzne dla PostgreSQL

Operatory PostgreSQL wykraczają poza standard: dla JSONB (->, ->>, @>, ?), tablic (&&, @>, <@), zakresów (@>, &&), tekstu (ILIKE, ~, *, !) oraz pełnotekstowego wyszukiwania (@@). Dzięki nim zapytania są krótsze i szybsze, szczególnie przy indeksach GIN/GIST.

Przykład użycia operatorów i ANY/ALL:

SQL
Kopiuj
1
2
3
4
5
6
7
8
-- Case-insensitive dopasowanie i regex
SELECT * FROM users WHERE email ILIKE '%@example.com' AND name ~ '^[A-Z][a-z]+$';

-- JSONB: sprawdzenie klucza i zawierania
SELECT * FROM docs WHERE meta ? 'tags' AND meta @> '{"published": true}';

-- Tablice i ANY
SELECT * FROM tickets WHERE status = ANY(ARRAY['open','pending']);

Jak pisać przenośny kod SQL

Jeśli celem jest przenośny kod SQL, unikaj funkcji i operatorów specyficznych dla PostgreSQL, gdy istnieje standardowy odpowiednik. Zamiast SERIAL użyj GENERATED {ALWAYS|BY DEFAULT} AS IDENTITY, zamiast DISTINCT ON użyj okien i filtrowania, zamiast ILIKE użyj LOWER(...) = LOWER(...). Rozważ MERGE dla upsertów (obsługiwany szeroko), a ON CONFLICT traktuj jako optymalizację dla PostgreSQL.

Dedup bez DISTINCT ON w sposób zbliżony do standardu SQL:

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
10
-- Zostawia ostatni wiersz per email
WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC) AS rn
  FROM users
)
SELECT id, email, updated_at
FROM ranked
WHERE rn = 1
FETCH FIRST 100 ROWS ONLY;

Wydajność i zgodność warto weryfikować testami na docelowych silnikach. O tym, jak korzystać z przenośnych mechanizmów analitycznych, przeczytasz w: Funkcje okienkowe w SQL: PostgreSQL i MySQL.