Gegevens opvragen

Voltooid

In deze les worden postgreSQL-specifieke queryfuncties en -patronen behandeld die essentieel zijn voor AI-toepassingen. De eenheid richt zich op de unieke mogelijkheden en geavanceerde patronen van PostgreSQL voor het beheren van gespreksgeschiedenis, verwerkingsstatus en toepassingsmetagegevens.

In deze les wordt ervan uitgegaan dat u bekend bent met standaard SQL-concepten, zoals SELECT, WHEREjoins en aggregatie. Als u een opfrisser nodig hebt, is het leertraject Aan de slag met Transact-SQL een goed hulpmiddel.

Queryuitvoering en aliasbereik

Zelfs ervaren SQL-ontwikkelaars worden in verwarring gebracht door de uitvoeringsvolgorde van SQL-queries, met name bij het gebruik van kolomaliassen. SQL-instructies worden uitgevoerd in een specifieke logische volgorde die verschilt van hoe u ze schrijft. Als u deze volgorde begrijpt, kunt u cryptische fouten 'kolom bestaat niet' oplossen.

Bestelling	Clausule	Purpose
1	FROM	Brontabellen identificeren
2	WHERE	Rijen filteren
3	GROUP BY	Rijen groeperen voor aggregatie
4	HAVING	Filtergroepen
5	SELECT	Kolommen kiezen en expressies berekenen
6	ORDER BY	Resultaten sorteren
7	LIMIT / OFFSET	Aantal resultaten beperken

Aliasbereikregel: Kolomaliassen die zijn gedefinieerd in SELECT zijn alleen zichtbaar voor clausules die worden uitgevoerd naSELECT, namelijk ORDER BY en LIMIT/OFFSET. Zinnen die eerder (WHERE, GROUP BY, HAVING) worden uitgevoerd, kunnen niet verwijzen naar deze aliassen omdat ze nog niet zijn gedefinieerd.

Dit betekent dat u niet kunt filteren op een alias in WHERE, maar u kunt er een sorteren 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;

PostgreSQL-specifieke filtering

PostgreSQL breidt standaard SQL uit met operators die algemene filtertaken vereenvoudigen. Deze functies zijn handig voor AI-toepassingen die flexibele zoekopdrachten voor tekst en gestructureerde metagegevensquery's nodig hebben.

De ILIKE operator biedt hoofdletterongevoelige patroonkoppeling zonder functies zoals LOWER(). Dit is handig voor gebruikersgerichte zoekfuncties waarbij u wilt overeenkomen, ongeacht hoofdlettergebruik: SELECT * FROM messages WHERE content ILIKE '%error%'.

Met PostgreSQL bepaalt u waar NULL waarden worden weergegeven in gesorteerde resultaten met behulp van NULLS FIRST of NULLS LAST. NULL Standaard sorteren waarden alsof ze groter zijn dan een andere waarde. Gebruik ORDER BY ended_at NULLS LAST om onvolledige gesprekken aan het einde te plaatsen, of ORDER BY completed_at NULLS FIRST om niet-verwerkte taken als eerste weer te geven.

De COALESCE functie retourneert de eerste niet-null-waarde uit de argumenten. Hiermee kunt u standaardwaarden opgeven in queryresultaten (COALESCE(title, 'Untitled')) of null-kolommen in expressies verwerken.

Query's uitvoeren op JSONB-gegevens

Het JSONB-type van PostgreSQL slaat gestructureerde gegevens op die niet passen bij een vast schema. AI-toepassingen maken vaak gebruik van JSONB voor metagegevens, configuratie, modelparameters en variabele responsstructuren. PostgreSQL biedt gespecialiseerde operators voor het extraheren van waarden, het controleren van de structuur en het filteren op basis van JSON-inhoud.

Hiermee -> extraheert u een JSON-element als JSON of ->> extraheert u het als tekst (voor vergelijkingen en weergave). Gebruik #> voor geneste paden (retourneert JSON) of #>> (retourneert tekst). Extraheert bijvoorbeeld metadata->>'status' het statusveld als tekst, terwijl checkpoint_data#>>'{results,0,score}' door een genest pad navigeert om een specifieke waarde op te halen.

Bestaans- en insluitingsoperators maken efficiënt filteren mogelijk. De ? operator controleert op het bestaan van de sleutel (WHERE metadata ? 'priority'), terwijl @> controleert op insluiting (WHERE checkpoint_data @> '{"status": "completed"}'). Deze operators kunnen GIN-indexen gebruiken voor efficiënt filteren op grote tabellen.

Wanneer JSONB-kolommen arrays bevatten, kunt u jsonb_array_elements gebruiken om ze uit te vouwen voor filteren of aggregatie.

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

Efficiënte paginering met sleutelsetpaginering

Traditionele OFFSETpaginering wordt traag voor grote tabellen, omdat PostgreSQL alle overgeslagen rijen moet scannen en negeren. Voor pagina 1.000 met 20 rijen per pagina moeten 20.000 rijen worden gescand en 19.980 worden verwijderd. Sleutelsetpaginering (ook wel op cursor gebaseerde paginering genoemd) maakt gebruik van WHERE clausules om rijen over te slaan, wat consistent presteert, ongeacht hoe diep u pagineert.

In plaats van paginanummers bij te houden, houd de laatste waarde bij die u hebt gezien en filter vanaf daar. Voor deze aanpak is een unieke, sorteerbare kolom (of combinatie van kolommen) vereist:

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

Inclusief id in zowel de ORDER BY- als de WHERE-clausules verwerkt gelijke tijdstempels wanneer meerdere rijen dezelfde tijdstempel hebben. Uw toepassing slaat de sorteerwaarden van de laatste rij op en geeft deze door aan de volgende query. Voor oplopende volgorde gaat u naar <> en DESC naar ASC.

Common Table Expressions (CTE's)

CTE's definiëren benoemde tijdelijke resultatensets die alleen bestaan tijdens de uitvoering van een query. Ze verbeteren de leesbaarheid door u stapsgewijs complexe query's te laten bouwen en recursieve query's in te schakelen voor hiërarchische gegevens.

Gebruik CTE's om complexe query's in logische stappen te splitsen. Elke CTE kan verwijzen naar eerder gedefinieerde CTE's, waardoor een pijplijn met transformaties wordt gemaakt:

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;

Recursieve CTE's voor het opvragen van boomstructuren (zoals taakhiërarchieën, organigrammen of discussielijnen). Ze bestaan uit een basiscase (anker) en een recursieve case die verwijst naar de CTE zelf. Neem altijd een dieptelimiet of een andere beëindigingsvoorwaarde op om oneindige lussen te voorkomen als uw gegevens cycli bevatten:

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-toepassingen moeten vaak gespreksthreads ophalen waarbij berichten verwijzen naar bovenliggende berichten:

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;

INVOEGEN met TERUGGAVE

Met de RETURNING clausule van PostgreSQL worden waarden opgehaald uit ingevoegde, bijgewerkte of verwijderde rijen in één enkele keer. Dit is essentieel voor het verkrijgen van automatisch gegenereerde id's, tijdstempels of berekende standaardwaarden zonder een afzonderlijke query.

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

Invoegen of bijwerken met ON CONFLICT

De INSERT ... ON CONFLICT component verwerkt unieke schendingen van beperkingen, waardoor upsert-bewerkingen worden ingeschakeld waarmee nieuwe rijen worden ingevoegd of bestaande rijen worden bijgewerkt. Dit patroon is waardevol voor idempotente bewerkingen en statusbeheer in AI-toepassingen.

Wanneer er een conflict optreedt bij een unieke constraint, kunt u de bestaande rij bijwerken met nieuwe waarden door DO UPDATE te gebruiken. De EXCLUDED pseudotabel verwijst naar de waarden die zouden zijn ingevoegd:

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;

Gebruik DO NOTHING om rijen stilletjes over te slaan die beperkingen schenden: INSERT INTO tags (name) VALUES ('important') ON CONFLICT (name) DO NOTHING.

U kunt een WHERE component toevoegen aan DO UPDATE voorwaardelijke logica, alleen bijwerken wanneer de nieuwe waarde verschilt van de bestaande. Combineer ON CONFLICT met RETURNING om te weten of een rij is ingevoegd of bijgewerkt: de expressie (xmax = 0) retourneert true voor nieuw ingevoegde rijen en false voor bijgewerkte rijen.

Praktische patronen voor AI-toepassingen

Deze patronen combineren de functies die eerder zijn behandeld in oplossingen voor algemene vereisten voor AI-toepassingen. Met de volgende query wordt de gespreksgeschiedenis opgehaald met metagegevens voor het bouwen van context:

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;

In het volgende voorbeeld ziet u controlepunten voor opnametaken met statusbeheer met 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;

In het volgende voorbeeld ziet u een gepagineerde zoekopdracht met JSONB-filters:

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;

Aanvullende bronnen

• PostgreSQL JSON-functies en -operators
• PostgreSQL INSERT-documentatie
• PostgreSQL WITH-queries (CTEs)