Interogarea datelor

Această unitate acoperă caracteristici și tipare specifice interogărilor PostgreSQL, esențiale pentru aplicațiile AI. Unitatea se concentrează pe capabilitățile unice ale PostgreSQL și pe modelele avansate pentru gestionarea istoricului conversațiilor, stării de procesare și metadatelor aplicației.

Această unitate presupune familiaritatea cu concepte SQL standard precum SELECT, WHERE, joins și agregare. Dacă ai nevoie de o recapitulare, calea de învățare Start Query cu Transact-SQL este o resursă bună.

Execuția interogării și scopul de tip alias

Chiar și dezvoltatorii SQL cu experiență se împiedică de ordinea execuției, mai ales când folosesc aliasuri de coloană. Instrucțiunile SQL rulează într-o ordine logică specifică care diferă de modul în care le scrii, iar înțelegerea acestei ordini te ajută să depanezi erori criptice de tipul "coloana nu există".

Regula privind domeniul de acoperire a aliasului: Aliasurile coloanelor definite în SELECT sunt vizibile doar pentru clauzele care se execută dupăSELECT — și anume ORDER BY și LIMIT/OFFSET. Clauzele care se execută anterior (WHERE, GROUP BY, HAVING) nu pot face referire la aceste aliase deoarece nu sunt încă definite.

Asta înseamnă că nu poți filtra după un alias în WHERE, dar poți sorta după unul în ORDER BY:

-- This FAILS: WHERE executes before SELECT, so 'msg_date' doesn't exist yet
SELECT DATE(created_at) AS msg_date, content
FROM messages
WHERE msg_date > '2024-01-01';  -- Error: column "msg_date" does not exist

-- This WORKS: repeat the expression in WHERE
SELECT DATE(created_at) AS msg_date, content
FROM messages
WHERE DATE(created_at) > '2024-01-01';

-- This also WORKS: ORDER BY executes after SELECT, so aliases are available
SELECT DATE(created_at) AS msg_date, content
FROM messages
WHERE DATE(created_at) > '2024-01-01'
ORDER BY msg_date;

Filtrarea specifică PostgreSQL

PostgreSQL extinde SQL-ul standard cu operatori care simplifică sarcinile comune de filtrare. Aceste funcții sunt utile pentru aplicațiile AI care necesită căutare textuală flexibilă și interogări structurate de metadate.

Operatorul ILIKE oferă potrivirea tiparelor insensible la majuscule fără a necesita funcții precum LOWER(). Acest lucru este util pentru funcțiile de căutare orientate către utilizator, unde vrei să potrivești indiferent de majuscule: SELECT * FROM messages WHERE content ILIKE '%error%'.

PostgreSQL îți permite să controlezi unde NULL apar valorile în rezultatele sortate folosind NULLS FIRST sau NULLS LAST. În mod implicit, NULL valorile se sortează ca și cum ar fi mai mari decât orice altă valoare. Folosește-le ORDER BY ended_at NULLS LAST pentru a păstra conversațiile incomplete la final sau ORDER BY completed_at NULLS FIRST pentru a arăta mai întâi sarcinile neprocesate.

Funcția COALESCE returnează prima valoare ne-nulă din argumentele sale. Folosiți-l pentru a furniza valori implicite în rezultatele interogărilor (COALESCE(title, 'Untitled')) sau pentru a gestiona coloanele anulabile în expresii.

Interoghează datele JSONB

Tipul JSONB al PostgreSQL stochează date structurate care nu se potrivesc unei scheme fixe. Aplicațiile AI folosesc frecvent JSONB pentru metadate, configurare, parametri de model și structuri de răspuns variabil. PostgreSQL oferă operatori specializați pentru extragerea valorilor, verificarea structurii și filtrarea pe baza conținutului JSON.

Folosiți -> pentru a extrage un element JSON ca JSON sau ->> pentru a-l extrage ca text (pentru comparații și afișare). Pentru căile imbricate, folosește #> (returnează JSON) sau #>> (returnează text). De exemplu, metadata->>'status' extrage câmpul de stare ca text, în timp checkpoint_data#>>'{results,0,score}' ce navighează pe o cale imbricată pentru a obține o valoare specifică.

Operatorii de existență și de izolare permit filtrarea eficientă. Operatorul ? verifică existența cheii (WHERE metadata ? 'priority'), în timp ce @> testează conținerea (WHERE checkpoint_data @> '{"status": "completed"}'). Acești operatori pot folosi indici GIN pentru filtrare eficientă pe tabele mari.

Când coloanele JSONB conțin tablou-uri, folosiți-le jsonb_array_elements pentru a le extinde pentru filtrare sau agregare:

-- Find conversations tagged with 'support'
SELECT DISTINCT c.*
FROM conversations c,
     jsonb_array_elements_text(c.metadata->'tags') AS tag
WHERE tag = 'support';

Paginare eficientă cu paginare prin keyset

Paginarea tradițională OFFSETdevine lentă pe tabele mari deoarece PostgreSQL trebuie să scaneze și să elimine toate rândurile sărite. Pagina 1.000 cu 20 de rânduri pe pagină necesită scanarea a 20.000 de rânduri și eliminarea a 19.980. Paginarea prin keyset (numită și paginare bazată pe cursor) folosește WHERE clauze pentru a sări peste rânduri, ceea ce funcționează constant indiferent cât de adânc paginați.

În loc să urmărești numerele de pagină, urmărește ultima valoare pe care ai văzut-o și filtrează de acolo. Această abordare necesită o coloană unică, sortabilă (sau o combinație de coloane):

-- First page: get the 20 most recent messages
SELECT id, conversation_id, content, created_at
FROM messages
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: filter by the last seen timestamp and id
SELECT id, conversation_id, content, created_at
FROM messages
WHERE (created_at, id) < ('2024-06-15 10:30:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Includerea id în ambele ORDER BY clauzele și WHERE gestionează egalitățile atunci când mai multe rânduri au aceeași marcă temporală. Aplicația ta stochează valorile de sortare ale ultimului rând și le transmite următoarei interogări. Pentru ordinea ascendentă, schimbă < la > și DESC la ASC.

Expresii comune ale tabelelor (CTE-uri)

CTE-urile definesc seturi temporare de rezultate denumite care există doar în timpul execuției unei interogări. Acestea îmbunătățesc lizibilitatea permițându-ți să construiești interogări complexe pas cu pas și permit interogări recursive pentru date ierarhice.

Folosește CTE-urile pentru a împărți interogările complexe în pași logici. Fiecare CTE poate face referire la CTE-uri definite anterior, creând un flux de transformări:

WITH recent_conversations AS (
    SELECT id, user_id, started_at
    FROM conversations
    WHERE started_at > CURRENT_DATE - INTERVAL '7 days'
),
message_stats AS (
    SELECT conversation_id, COUNT(*) AS message_count, MAX(created_at) AS last_message_at
    FROM messages
    GROUP BY conversation_id
)
SELECT rc.user_id, rc.started_at, COALESCE(ms.message_count, 0) AS message_count
FROM recent_conversations rc
LEFT JOIN message_stats ms ON rc.id = ms.conversation_id;

CTE-urile recursive includ structuri de arbore de interogare precum ierarhii de sarcini, organigrame sau conversații în fir de execuție. Acestea constau într-un caz de bază (ancoră) și un caz recursiv care face referire la CTE în sine. Include întotdeauna o limită de adâncime sau o altă condiție de terminare pentru a preveni bucle infinite dacă datele tale conțin cicluri:

WITH RECURSIVE task_tree AS (
    -- Base case: start with the parent task
    SELECT id, parent_id, title, 1 AS depth
    FROM tasks WHERE id = 1
    UNION ALL
    -- Recursive case: find children of current level
    SELECT t.id, t.parent_id, t.title, tt.depth + 1
    FROM tasks t
    INNER JOIN task_tree tt ON t.parent_id = tt.id
    WHERE tt.depth < 10
)
SELECT * FROM task_tree ORDER BY depth, id;

Aplicațiile AI trebuie adesea să recupereze fire de conversație unde mesajele fac referire la mesajele părinte:

WITH RECURSIVE thread AS (
    -- Start with the root message
    SELECT id, parent_id, content, role, 0 AS depth
    FROM messages
    WHERE id = :root_message_id

    UNION ALL

    -- Get all replies
    SELECT m.id, m.parent_id, m.content, m.role, t.depth + 1
    FROM messages m
    INNER JOIN thread t ON m.parent_id = t.id
    WHERE t.depth < 50
)
SELECT * FROM thread ORDER BY depth, id;

INSERT cu RETURN

Clauza PostgreSQL RETURNING recuperează valori din rândurile inserate, actualizate sau șterse într-o singură călătorie dus-întors. Acest lucru este esențial pentru a obține ID-uri generate automat, timestamp-uri sau setări implicite calculate fără o interogare separată.

-- Get the generated ID after inserting a conversation
INSERT INTO conversations (user_id, session_id)
VALUES ('user123', 'sess_abc')
RETURNING id;

-- Get multiple generated values
INSERT INTO messages (conversation_id, role, content)
VALUES (1, 'user', 'Hello')
RETURNING id, created_at;

-- Use RETURNING with UPDATE
UPDATE tasks SET status = 'completed', completed_at = CURRENT_TIMESTAMP
WHERE id = 5
RETURNING id, status, completed_at;

Îmbunătățiri cu ON CONFLICT

Clauza INSERT ... ON CONFLICT gestionează încălcări unice ale constrângerilor, permițând operații "upsert" care inserează rânduri noi sau actualizează rânduri existente. Acest tipar este valoros pentru operațiunile idempotente și managementul stării în aplicațiile AI.

Când apare un conflict pe o singură constrângere, poți actualiza rândul existent cu valori noi folosind DO UPDATE. EXCLUDED Pseudo-tabelul face referire la valorile care ar fi fost inserate:

INSERT INTO user_preferences (user_id, preference_key, preference_value)
VALUES ('user123', 'theme', 'dark')
ON CONFLICT (user_id, preference_key)
DO UPDATE SET
    preference_value = EXCLUDED.preference_value,
    updated_at = CURRENT_TIMESTAMP;

Folosiți DO NOTHING pentru a sări în tăcere rânduri care ar încălca constrângerile: INSERT INTO tags (name) VALUES ('important') ON CONFLICT (name) DO NOTHING.

Poți adăuga o WHERE clauză la DO UPDATE pentru logica condiționată, actualizându-se doar când noua valoare diferă de cea existentă. Combină ON CONFLICT cu RETURNING pentru a ști dacă un rând a fost inserat sau actualizat — expresia (xmax = 0) returnează true pentru rândurile nou inserate și false pentru rândurile actualizate.

Modele practice pentru aplicații AI

Aceste tipare combină caracteristicile menționate anterior în soluții pentru cerințele comune ale aplicațiilor AI. Următoarea interogare recuperează istoricul conversațiilor cu metadate pentru construirea contextului:

WITH conversation_context AS (
    SELECT c.id, c.session_id, c.metadata->>'model' AS model
    FROM conversations c
    WHERE c.session_id = :session_id AND c.ended_at IS NULL
)
SELECT cc.session_id, cc.model, m.role, m.content, m.created_at
FROM conversation_context cc
INNER JOIN messages m ON cc.id = m.conversation_id
ORDER BY m.created_at
LIMIT 50;

Următorul exemplu arată înregistrarea punctelor de verificare a sarcinilor cu managementul stării folosind upsert:

INSERT INTO task_checkpoints (task_id, step_number, checkpoint_data)
VALUES (:task_id, :step_number, :checkpoint_json::jsonb)
ON CONFLICT (task_id, step_number)
DO UPDATE SET checkpoint_data = EXCLUDED.checkpoint_data, updated_at = CURRENT_TIMESTAMP
RETURNING id, created_at, (xmax = 0) AS is_new;

Următorul exemplu arată o căutare paginată cu filtre JSONB:

SELECT c.id, c.session_id, c.started_at, c.metadata->>'status' AS status
FROM conversations c
WHERE c.user_id = :user_id
  AND c.metadata @> :filter_json::jsonb
  AND (c.started_at, c.id) < (:last_started_at, :last_id)
ORDER BY c.started_at DESC, c.id DESC
LIMIT 20;

Resurse suplimentare

• Funcții și operatori JSON PostgreSQL
• Documentația PostgreSQL INSERT
• PostgreSQL WITH interogări (CTE)