Dati di query

Questa unità illustra le funzionalità e i modelli di query specifici di PostgreSQL essenziali per le applicazioni di intelligenza artificiale. L'unità è incentrata sulle funzionalità esclusive di PostgreSQL e sui modelli avanzati per la gestione della cronologia delle conversazioni, dello stato di elaborazione e dei metadati dell'applicazione.

Questa unità presuppone la familiarità con i concetti SQL standard, ad esempio SELECT, WHERE, join e aggregazione. Se è necessario un aggiornamento, il percorso di apprendimento Introduzione all'esecuzione di query con Transact-SQL è una risorsa valida.

Esecuzione di query e ambito alias

Anche gli sviluppatori SQL esperti si confondono con l'ordine di esecuzione, in particolare quando utilizzano alias di colonna. Le istruzioni SQL vengono eseguite in un ordine logico specifico che differisce da come vengono scritte e la comprensione di questo ordine consente di risolvere gli errori criptici "la colonna non esiste".

Regola di ambito alias: Gli alias di colonna definiti in SELECT sono visibili solo alle clausole eseguite dopoSELECT, vale a dire ORDER BY e LIMIT/OFFSET. Le clausole eseguite in precedenza (WHERE, GROUP BY, HAVING) non possono fare riferimento a questi alias perché non sono ancora definite.

Ciò significa che non è possibile filtrare in base a un alias in WHERE, ma è possibile ordinare per uno in 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;

Filtro specifico di PostgreSQL

PostgreSQL estende SQL standard con operatori che semplificano le attività di filtro comuni. Queste funzionalità sono utili per le applicazioni di intelligenza artificiale che richiedono ricerche di testo flessibili e query di metadati strutturate.

L'operatore ILIKE fornisce criteri di ricerca senza distinzione tra maiuscole e minuscole senza richiedere funzioni come LOWER(). Ciò è utile per le funzionalità di ricerca rivolte agli utenti in cui si vuole trovare una corrispondenza indipendentemente dalla combinazione di maiuscole e minuscole: SELECT * FROM messages WHERE content ILIKE '%error%'.

PostgreSQL consente di controllare dove i valori NULL appaiono nei risultati ordinati utilizzando NULLS FIRST o NULLS LAST. Per impostazione predefinita, NULL i valori vengono ordinati come se fosse maggiore di qualsiasi altro valore. Usare ORDER BY ended_at NULLS LAST per mantenere le conversazioni incomplete alla fine o ORDER BY completed_at NULLS FIRST per visualizzare prima le attività non elaborate.

La COALESCE funzione restituisce il primo valore non Null dai relativi argomenti. Usarlo per fornire valori predefiniti nei risultati della query (COALESCE(title, 'Untitled')) o per gestire colonne nullable nelle espressioni.

Eseguire query sui dati JSONB

Il tipo JSONB di PostgreSQL archivia i dati strutturati che non soddisfano uno schema fisso. Le applicazioni di intelligenza artificiale usano in genere JSONB per metadati, configurazione, parametri del modello e strutture di risposta variabili. PostgreSQL fornisce operatori specializzati per estrarre valori, controllare la struttura e filtrare in base al contenuto JSON.

Usare -> per estrarre un elemento JSON come JSON o ->> per estrarlo come testo (per confronti e visualizzazione). Per i percorsi annidati, usare #> (restituisce JSON) o #>> (restituisce testo). Ad esempio, metadata->>'status' estrae il campo di stato come testo, mentre checkpoint_data#>>'{results,0,score}' esplora un percorso annidato per ottenere un valore specifico.

Gli operatori di esistenza e contenimento consentono di applicare filtri in modo efficiente. L'operatore ? verifica l'esistenza della chiave (WHERE metadata ? 'priority'), mentre @> verifica il contenimento (WHERE checkpoint_data @> '{"status": "completed"}'). Questi operatori possono usare indici GIN per filtrare in modo efficiente le tabelle di grandi dimensioni.

Quando le colonne JSONB contengono matrici, usare jsonb_array_elements per espanderle per filtrare o aggregare:

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

Impaginazione efficiente con keyset pagination

La paginazione basata su OFFSET diventa lenta nelle tabelle di grandi dimensioni perché PostgreSQL deve esaminare ed eliminare tutte le righe saltate. La pagina 1.000 con 20 righe per pagina richiede l'analisi di 20.000 righe e l'eliminazione di 19.980. La paginazione keyset (detta anche paginazione basata su cursore) usa clausole WHERE per ignorare le righe, che vengono eseguite in modo coerente indipendentemente dal livello di impaginazione.

Invece di tenere traccia dei numeri di pagina, tenere traccia dell'ultimo valore visualizzato e filtrare da questa posizione. Questo approccio richiede una colonna univoca ordinabile (o combinazione di colonne):

-- 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;

L'inclusione di id in entrambe le clausole ORDER BY e WHERE gestisce i collegamenti quando più righe hanno lo stesso timestamp. L'applicazione archivia i valori di ordinamento dell'ultima riga e li passa alla query successiva. Per l'ordine crescente, passare < a > e DESC a ASC.

Espressioni di tabella comuni

Le CTE definiscono set di risultati temporanei denominati esistenti solo durante l'esecuzione di una query. Migliorano la leggibilità consentendo di creare query complesse passo dopo passo e abilitano query ricorsive per i dati gerarchici.

Usare le CTE per suddividere query complesse in passaggi logici. Ogni CTE può fare riferimento alle CTE definite in precedenza, creando una pipeline di trasformazioni:

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;

Strutture ad albero delle query CTE ricorsive, ad esempio gerarchie di attività, organigrammi o conversazioni in thread. Sono costituiti da un caso di base (ancoraggio) e da un caso ricorsivo che fa riferimento al CTE stesso. Includere sempre un limite di profondità o un'altra condizione di terminazione per impedire cicli infiniti se i dati contengono cicli:

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;

Le applicazioni di intelligenza artificiale spesso devono recuperare i thread di conversazione in cui i messaggi fanno riferimento ai messaggi padre:

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 con RETURNING

La clausola RETURNING di PostgreSQL recupera i valori dalle righe inserite, aggiornate o eliminate in un unico "round trip". Questo è essenziale per ottenere ID generati automaticamente, timestamp o impostazioni predefinite calcolate senza una query separata.

-- 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;

Operazioni di inserimento/aggiornamento con ON CONFLICT

La INSERT ... ON CONFLICT clausola gestisce violazioni di vincoli univoche, abilitando le operazioni "upsert" che inseriscono nuove righe o aggiornano quelle esistenti. Questo modello è utile per le operazioni idempotenti e la gestione dello stato nelle applicazioni di intelligenza artificiale.

Quando si verifica un conflitto in un vincolo univoco, è possibile aggiornare la riga esistente con nuovi valori usando DO UPDATE. La EXCLUDED pseudo-tabella fa riferimento ai valori che sarebbero stati inseriti:

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;

Usare DO NOTHING per ignorare automaticamente le righe che violano i vincoli: INSERT INTO tags (name) VALUES ('important') ON CONFLICT (name) DO NOTHING.

È possibile aggiungere una WHERE clausola a DO UPDATE per la logica condizionale, aggiornando solo quando il nuovo valore è diverso da quello esistente. Combinare ON CONFLICT con RETURNING per sapere se una riga è stata inserita o aggiornata, ovvero l'espressione (xmax = 0) restituisce true per le righe appena inserite e false per le righe aggiornate.

Modelli pratici per le applicazioni di intelligenza artificiale

Questi modelli combinano le funzionalità descritte in precedenza nelle soluzioni per i requisiti comuni delle applicazioni di intelligenza artificiale. La query seguente recupera la cronologia delle conversazioni con i metadati per la compilazione del contesto:

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;

L'esempio seguente mostra la registrazione dei punti di controllo delle attività con la gestione degli stati utilizzando 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;

L'esempio seguente mostra una ricerca impaginata con filtri 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;

Risorse aggiuntive

• Funzioni e operatori JSON postgreSQL
• Documentazione di PostgreSQL INSERT
• Query PostgreSQL WITH (CTE)