dexblog.ro
Acces rapid: Învață AI Învață Programare Specificații Dispozitive Comparații
PGSQL

Învață PostgreSQL: interogări SQL, JSONB, Window Functions și psql explicate cu exemple

28 de comenzi 6 categorii

PostgreSQL (adesea prescurtat Postgres) este cel mai avansat sistem de gestiune a bazelor de date relaționale și obiectual-relaționale open-source, apreciat pentru conformitatea strictă cu standardul SQL, extensibilitate, robustețea tranzacțiilor ACID și suportul nativ pentru date semistructurate (JSONB, tablouri ARRAY, căutare full-text și tipuri geometrice).

Această secțiune reunește cele mai utile construcții SQL specifice PostgreSQL — de la DISTINCT ON, CROSS JOIN LATERAL, RETURNING și ON CONFLICT (UPSERT), până la expresii de tabel comune (WITH RECURSIVE), funcții fereastră (Window Functions), operatori JSONB, indecși GIN și comenzi de administrare în terminalul psql. Apasă pe oricare comandă sau funcție din tabelul de mai jos pentru sintaxă completă, explicații detaliate, exemple cu rezultate în format psql și capcane frecvente de evitat.

macOS • Windows • Linux

Ghid de instalare PostgreSQL și clientul psql pe fiecare sistem de operare

Pornește PostgreSQL 16/17 local pe macOS, Windows, Linux sau în Docker și conectează-te din terminal cu psql.

Cerințe de sistem: Portul implicit 5432 liber; opțional Docker pentru rulare izolată fără instalare în sistem.

Terminal macOS (Homebrew / zsh) 
# Instalare PostgreSQL 16 prin Homebrew și pornire serviciu:
brew install postgresql@16
brew services start postgresql@16

# Conectare în consola interactivă psql și creare bază de date:
psql postgres
CREATE DATABASE magazin;
\c magazin

Interogări și filtrare specifică Postgres 5 comenzi

Modificare date și tranzacții 5 comenzi

CTE și Window Functions 5 comenzi

JSONB, Array și tipuri avansate 5 comenzi

Date, timp și generare serii 3 comenzi

Indecși, performanță și terminalul psql 5 comenzi

Toate comenzile și funcțiile PostgreSQL

Lista completă a elementelor explicate în această secțiune, cu sintaxa lor uzuală. Fiecare element are o pagină dedicată, cu explicații detaliate, parametri și exemple practice.

Interogări și filtrare specifică Postgres (5)

Comandă / Funcție Ce face
SELECT și DISTINCT ON Extrage rânduri din tabele și păstrează doar primul rând pentru fiecare grup de valori distincte.
SELECT DISTINCT ON (expresie [, ...]) lista_coloane FROM tabel ORDER BY expresie [, ...], criteriu_sortare;
WHERE, ILIKE și expresii POSIX (~) Filtrează rândurile folosind șabloane insensibile la majuscule (ILIKE) și expresii regulare POSIX (~, ~*).
SELECT * FROM tabel WHERE coloana ILIKE 'sablon%' OR coloana ~* 'expresie_regulara';
LIMIT, OFFSET și FETCH FIRST Limitează numărul de rânduri returnate și implementează paginarea sau extragerea top N cu egalități.
SELECT * FROM tabel ORDER BY coloana LIMIT n OFFSET m;\nSELECT * FROM tabel ORDER BY coloana FETCH FIRST n ROWS WITH TIES;
JOIN și CROSS JOIN LATERAL Unește tabele și permite subinterogărilor din FROM să facă referire la coloanele tabelelor precedente prin LATERAL.
SELECT ... FROM tabel_stanga t1 LEFT JOIN LATERAL (SELECT ... WHERE col = t1.id LIMIT n) t2 ON true;
GROUP BY, HAVING și clauza FILTER Grupează rândurile pentru agregări și aplică condiții individuale pe fiecare funcție agregată prin FILTER (WHERE …).
SELECT col, COUNT(*) FILTER (WHERE conditie) FROM tabel GROUP BY col HAVING COUNT(*) > n;

Modificare date și tranzacții (5)

Comandă / Funcție Ce face
INSERT INTO ... RETURNING Inserează unul sau mai multe rânduri și returnează imediat valorile generate (ID-uri, timestamp-uri) fără o interogare suplimentară.
INSERT INTO tabel (col1, col2) VALUES (val1, val2) [, ...] RETURNING id, col1, creat_la;
ON CONFLICT DO UPDATE (UPSERT) Rezolvă atomic conflictele de cheie unică la inserare: ignoră rândul duplicat sau îl actualizează pe loc.
INSERT INTO tabel (col_unica, val) VALUES (...) ON CONFLICT (col_unica) DO UPDATE SET val = EXCLUDED.val;
UPDATE ... FROM Actualizează rândurile unui tabel folosind date dintr-un alt tabel sau dintr-o listă VALUES prin clauza FROM.
UPDATE tabel_tinta t SET col = s.val FROM tabel_sursa s WHERE t.id = s.id RETURNING t.*;
DELETE ... USING și TRUNCATE Șterge rânduri filtrate prin joncțiuni cu DELETE … USING sau golește instantaneu tabele întregi cu TRUNCATE.
DELETE FROM tabel_tinta t USING tabel_sursa s WHERE t.id = s.id RETURNING t.id;\nTRUNCATE TABLE tabel RESTART IDENTITY CASCADE;
BEGIN, COMMIT, ROLLBACK și SAVEPOINT Controlează tranzacțiile ACID și punctele intermediare de salvare (SAVEPOINT) pentru recuperarea din erori.
BEGIN;\nSAVEPOINT sp1;\n-- interogări SQL\nROLLBACK TO SAVEPOINT sp1;\nCOMMIT;

CTE și Window Functions (5)

Comandă / Funcție Ce face
WITH (Common Table Expressions) Definește subinterogări numite (CTE) pentru structurarea clară a interogărilor complexe și înlănțuirea operațiilor DML.
WITH nume_cte AS [MATERIALIZED | NOT MATERIALIZED] (SELECT ...) SELECT * FROM nume_cte;
WITH RECURSIVE (ierarhii și arbori) Parcurge structuri arborescente, organigrame, categorii imbricate și grafuri direct în SQL.
WITH RECURSIVE arbore AS (SELECT ... /* ancora */ UNION ALL SELECT ... FROM tabel JOIN arbore ON ...) SELECT * FROM arbore;
ROW_NUMBER(), RANK() și DENSE_RANK() Numerotează și clasifică rândurile în interiorul unei partiții fără a le colapsa ca GROUP BY.
ROW_NUMBER() OVER (PARTITION BY col_grup ORDER BY col_sortare DESC)
LAG() și LEAD() Accesează valoarea unei coloane de pe rândul anterior (LAG) sau următor (LEAD) fără a face self-join.
LAG(expresie [, offset [, valoare_implicita]]) OVER (PARTITION BY ... ORDER BY ...)
SUM() / AVG() OVER (PARTITION BY) Calculează totaluri cumulate (running totals), medii mobile și procente din totalul grupului folosind ferestre.
SUM(col) OVER (PARTITION BY grup ORDER BY data ROWS BETWEEN n PRECEDING AND CURRENT ROW)

JSONB, Array și tipuri avansate (5)

Comandă / Funcție Ce face
JSONB și operatorii ->, ->>, @>, ? Interoghează și filtrează documente binare JSONB folosind operatorii de extracție (->, ->>) și incluziune (@>, ?).
SELECT date_jsonb->>'cheie', date_jsonb->'obiect'->>'subcheie' FROM tabel WHERE date_jsonb @> '{"activ": true}';
jsonb_build_object, jsonb_agg și jsonb_set Construiește obiecte și vectori JSONB din rânduri relaționale și modifică parțial documente JSONB existente.
jsonb_build_object('cheie', col);\njsonb_agg(expresie ORDER BY col);\njsonb_set(tinta, '{cale,cheie}', valoare_noua_jsonb, true);
ARRAY, ANY() și unnest() Stochează și manipulează tablouri tipizate (text[], int[]), verifică apartenența cu ANY() și le extinde în rânduri cu unnest().
SELECT * FROM tabel WHERE 'valoare' = ANY(coloana_array);\nSELECT id, elem FROM tabel CROSS JOIN LATERAL unnest(coloana_array) AS elem;
gen_random_uuid() și GENERATED AS IDENTITY Definește chei primare moderne cu coloane auto-incrementate standard SQL (IDENTITY) sau identificatori unici globali (UUID v4).
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\nid_public uuid DEFAULT gen_random_uuid() NOT NULL UNIQUE
COALESCE, NULLIF și CASE WHEN Gestionează valorile NULL, previne împărțirea la zero și evaluează logica condițională direct în expresiile SQL.
COALESCE(val1, val2, 'implicit');\nNULLIF(expresie, 0);\nCASE WHEN cond1 THEN rez1 ELSE rez_default END;

Date, timp și generare serii (3)

Comandă / Funcție Ce face
NOW(), INTERVAL și date_trunc() Lucrează cu momente de timp cu fus orar (timestamptz), aritmetică de durate (INTERVAL) și rotunjire calendaristică (date_trunc).
SELECT NOW() - INTERVAL '7 days', date_trunc('month', NOW());
to_char() și EXTRACT() Formatează date, ore și numere în șiruri personalizate (to_char) sau extrage componente numerice din timestamp-uri (EXTRACT).
to_char(timestamp, 'YYYY-MM-DD HH24:MI:SS');\nEXTRACT(YEAR FROM timestamp);\nEXTRACT(EPOCH FROM interval);
generate_series() Generează pe loc serii de numere întregi sau calendare de date/ore pentru rapoarte fără zile lipsă și date de test.
SELECT * FROM generate_series(start, stop [, pas]);

Indecși, performanță și terminalul psql (5)

Comandă / Funcție Ce face
CREATE INDEX (B-Tree, GIN, Partial, CONCURRENTLY) Creează indecși B-Tree, GIN (pentru JSONB și Array), indecși parțiali (WHERE) și indecși pe expresii fără blocarea scrierilor.
CREATE INDEX CONCURRENTLY idx_nume ON tabel USING gin (coloana_jsonb);\nCREATE INDEX idx_partial ON tabel (col) INCLUDE (col_extra) WHERE activ = true;
EXPLAIN (ANALYZE, BUFFERS) Afișează planul de execuție al optimizatorului, timpii reali pe fiecare nod și blocurile de memorie citite.
EXPLAIN (ANALYZE, BUFFERS, TIMING, FORMAT TEXT) SELECT ...;
VACUUM și ANALYZE Recuperează spațiul ocupat de rândurile șterse/modificate (MVCC dead tuples) și actualizează statisticile pentru optimizator.
VACUUM (VERBOSE, ANALYZE) nume_tabel;\nANALYZE VERBOSE nume_tabel;
Comenzi psql (\l, \c, \dt, \d, \timing, \copy) Navighează rapid prin baze de date, tabele, scheme și exportă/importă CSV direct din clientul interactiv de terminal psql.
psql -h host -U utilizator -d baza_de_date\n\l \c db \dt+ \d+ tabel \timing \x auto \copy
pg_dump și pg_restore Creează copii de siguranță (backup) consistente online și restaurează selectiv sau în paralel bazele de date PostgreSQL.
pg_dump -h host -U user -Fc -f backup.dump nume_db\npg_restore -h host -U user -d nume_db -j 4 backup.dump