Forespørg på data

Denne enhed dækker PostgreSQL-specifikke forespørgselsfunktioner og -mønstre, som er essentielle for AI-applikationer. Enheden fokuserer på PostgreSQL's unikke kapaciteter og avancerede mønstre til håndtering af samtalehistorik, behandlingstilstand og applikationsmetadata.

Denne enhed forudsætter kendskab til standard SQL-koncepter som SELECT, WHERE, joins og aggregation. Hvis du har brug for en opfriskning, er Get Start querying with Transact-SQL læringsvejen en god ressource.

Forespørgselsudførelse og alias-scope

Selv erfarne SQL-udviklere bliver snublet over eksekveringsrækkefølgen, især når de bruger kolonnealiaser. SQL-udsagn kører i en bestemt logisk rækkefølge, der adskiller sig fra, hvordan du skriver dem, og forståelsen af denne rækkefølge hjælper dig med at fejlfinde kryptiske "kolonnen eksisterer ikke"-fejl.

Regel om alias-omfang: Kolonnealiaser defineret i SELECT er kun synlige for klausuler, der udføres efterSELECT—nemlig ORDER BY og LIMIT/OFFSET. Klausuler, der eksekverer tidligere (WHERE, GROUP BY, ), HAVINGkan ikke referere til disse aliaser, fordi de endnu ikke er definerede.

Det betyder, at du ikke kan filtrere efter et alias i WHERE, men du kan sortere efter et i 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;

PostgreSQL-specifik filtrering

PostgreSQL udvider standard SQL med operatorer, der forenkler almindelige filtreringsopgaver. Disse funktioner er nyttige for AI-applikationer, der har brug for fleksibel tekstsøgning og strukturerede metadataforespørgsler.

Operatoren ILIKE giver kasus-ufølsom mønstergenkendelse uden at kræve funktioner som LOWER(). Dette er nyttigt for brugerrettede søgefunktioner, hvor du ønsker at matche uanset store bogstaver: SELECT * FROM messages WHERE content ILIKE '%error%'.

PostgreSQL lader dig styre, hvor NULL værdier vises i sorterede resultater ved hjælp af NULLS FIRST eller NULLS LAST. Som standard sorteres værdierne, NULL som om de er større end nogen anden værdi. Brug ORDER BY ended_at NULLS LAST den til at holde ufuldstændige samtaler til sidst, eller ORDER BY completed_at NULLS FIRST til at vise ubehandlede opgaver først.

Funktionen COALESCE returnerer den første ikke-nulværdi fra sine argumenter. Brug det til at angive standardværdier i forespørgselsresultater (COALESCE(title, 'Untitled')) eller til at håndtere nullbare kolonner i udtryk.

Forespørg JSONB-data

PostgreSQL's JSONB-type gemmer strukturerede data, der ikke passer ind i et fast skema. AI-applikationer bruger ofte JSONB til metadata, konfiguration, modelparametre og variable responsstrukturer. PostgreSQL leverer specialiserede operatorer til at udtrække værdier, kontrollere struktur og filtrere baseret på JSON-indhold.

Brug -> til at udtrække et JSON-element som JSON, eller ->> til at udtrække det som tekst (til sammenligninger og visning). For indlejrede stier bruges #> (returnerer JSON) eller #>> (returnerer tekst). For eksempel metadata->>'status' udtrækker statusfeltet som tekst, mens det checkpoint_data#>>'{results,0,score}' navigerer en indlejret sti for at få en specifik værdi.

Eksistens- og indeslutningsoperatorer muliggør effektiv filtrering. Operatoren ? tjekker for nøglens eksistens (WHERE metadata ? 'priority'), mens @> den tester indeslutningen (WHERE checkpoint_data @> '{"status": "completed"}'). Disse operatorer kan bruge GIN-indekser til effektiv filtrering på store tabeller.

Når JSONB-kolonner indeholder arrays, bruges jsonb_array_elements til at udvide dem til filtrering eller aggregering:

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

Effektiv paginering med keyset-paginering

Traditionel OFFSETpaginering baseret på sidetegning bliver langsom på store tabeller, fordi PostgreSQL skal scanne og kassere alle oversprangne rækker. Side 1.000 med 20 rækker pr. side kræver scanning af 20.000 rækker og smidning af 19.980. Keyset-paginering (også kaldet cursor-baseret paginering) bruger WHERE klausuler til at springe rækker over, hvilket fungerer konsekvent uanset hvor dybt du paginerer.

I stedet for at spore sidenumre, spor den sidste værdi, du så, og filtrer derfra. Denne tilgang kræver en entydig, sorterbar kolonne (eller kombination af kolonner):

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

Inkludering id i både og-klausulerne ORDER BYWHERE håndterer uafgjorte, når flere rækker har samme tidsstempel. Din applikation gemmer sorteringsværdierne fra den sidste række og sender dem videre til næste forespørgsel. For stigende rækkefølge skift < til > og DESC til ASC.

Almindelige tabeludtryk (CTE'er)

CTE'er definerer navngivne midlertidige resultatsæt, der kun eksisterer under udførelsen af en forespørgsel. De forbedrer læsbarheden ved at lade dig bygge komplekse forespørgsler trin for trin, og de muliggør rekursive forespørgsler for hierarkiske data.

Brug CTE'er til at bryde komplekse forespørgsler op i logiske trin. Hver CTE kan referere til tidligere definerede CTE'er og skabe en pipeline af transformationer:

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;

Rekursive CTE'er forespørgselstræstrukturer som opgavehierarkier, organisationsdiagrammer eller trådede samtaler. De består af et basistilfælde (anker) og et rekursivt tilfælde, der refererer til selve CTE'en. Inkluder altid en dybdebegrænsning eller anden afslutningsbetingelse for at forhindre uendelige løkker, hvis dine data indeholder cyklusser:

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;

AI-applikationer skal ofte hente samtaletråde, hvor beskeder refererer til forældrebeskeder:

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;

INDSÆT med TILBAGEVENDENDE

PostgreSQL's RETURNING klausul henter værdier fra indsatte, opdaterede eller slettede rækker i en enkelt rundtur. Dette er essentielt for at få autogenererede ID'er, tidsstempler eller beregnede standardindstillinger uden en separat forespørgsel.

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

Upserts med ON CONFLICT

Klausulen INSERT ... ON CONFLICT håndterer unikke begrænsninger-overtrædelser og muliggør "upsert"-operationer, der indsætter nye rækker eller opdaterer eksisterende. Dette mønster er værdifuldt for idempotente operationer og tilstandsstyring i AI-applikationer.

Når der opstår en konflikt på en unik begrænsning, kan du opdatere den eksisterende række med nye værdier ved hjælp af DO UPDATE. Pseudo-tabellen EXCLUDED refererer til de værdier, der ville være indsat:

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;

Brug DO NOTHING til lydløst at springe rækker over, der ville overtræde begrænsninger: INSERT INTO tags (name) VALUES ('important') ON CONFLICT (name) DO NOTHING.

Du kan tilføje en WHERE klausul til DO UPDATE for betinget logik, som kun opdateres, når den nye værdi adskiller sig fra den eksisterende. Kombiner ON CONFLICT med RETURNING for at vide, om en række er indsat eller opdateret—udtrykket (xmax = 0) returnerer true for nyindsatte rækker og false for opdaterede rækker.

Praktiske mønstre for AI-applikationer

Disse mønstre kombinerer de tidligere dækkede funktioner til løsninger for almindelige AI-applikationskrav. Følgende forespørgsel henter samtalehistorik med metadata til kontekstopbygning:

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;

Følgende eksempel viser registrering af opgavecheckpoints med tilstandsstyring ved brug af 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;

Følgende eksempel viser en pagineret søgning med JSONB-filtre:

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;

Yderligere ressourcer

• PostgreSQL JSON-funktioner og operatorer
• PostgreSQL INSERT dokumentation
• PostgreSQL MED forespørgsler (CTE'er)