Rechercher des données

12 minutes

Cette unité couvre les fonctionnalités et les modèles de requête spécifiques à PostgreSQL qui sont essentiels pour les applications IA. L’unité se concentre sur les fonctionnalités uniques et les modèles avancés de PostgreSQL pour gérer l’historique des conversations, l’état de traitement et les métadonnées d’application.

Cette unité suppose une connaissance des concepts SQL standard tels que SELECT, les WHEREjointures et l’agrégation. Si vous avez besoin d’un rappel, le parcours d'apprentissage Commencer à interroger avec Transact-SQL est une bonne ressource.

Exécution des requêtes et étendue d’alias

Même les développeurs SQL expérimentés se font piéger par l'ordre d'exécution, notamment lors de l'utilisation d'alias de colonne. Les instructions SQL s’exécutent dans un ordre logique spécifique qui diffère de la façon dont vous les écrivez, et la compréhension de cet ordre vous aide à résoudre les erreurs cryptiques « colonne n’existe pas ».

JSON	Clause	Objectif
1	`FROM`	Identifier les tables sources
2	`WHERE`	Filtrer des lignes
3	`GROUP BY`	Regrouper des lignes pour l’agrégation
4	`HAVING`	Groupes de filtres
5	`SELECT`	Choisir des colonnes et des expressions de calcul
6	`ORDER BY`	Trier les résultats
7	`LIMIT` / `OFFSET`	Limiter le nombre de résultats

Règle d’étendue d’alias : Les alias de colonne définis dans SELECT ne sont visibles que par les clauses qui s’exécutent aprèsSELECT : à savoir ORDER BY et LIMIT/OFFSET. Les clauses qui s’exécutent précédemment (WHERE, GROUP BY, HAVING) ne peuvent pas référencer ces alias, car elles ne sont pas encore définies.

Cela signifie que vous ne pouvez pas filtrer par un alias dans WHERE, mais vous pouvez trier par un dans 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;

Filtrage spécifique à PostgreSQL

PostgreSQL étend sql standard avec des opérateurs qui simplifient les tâches de filtrage courantes. Ces fonctionnalités sont utiles pour les applications IA qui ont besoin de requêtes de recherche de texte flexibles et de métadonnées structurées.

L’opérateur ILIKE permet une mise en correspondance de motifs insensible à la casse sans nécessiter de fonctions telles que LOWER(). Cela est utile pour les fonctionnalités de recherche accessibles par l’utilisateur dans lesquelles vous souhaitez correspondre indépendamment de la mise en majuscules : SELECT * FROM messages WHERE content ILIKE '%error%'.

PostgreSQL vous permet de contrôler où NULL les valeurs apparaissent dans les résultats triés à l’aide NULLS FIRST ou NULLS LAST. Par défaut, NULL les valeurs trient comme si elles sont supérieures à n’importe quelle autre valeur. Permet ORDER BY ended_at NULLS LAST de conserver des conversations incomplètes à la fin ou ORDER BY completed_at NULLS FIRST d’afficher d’abord les tâches non traitées.

La COALESCE fonction retourne la première valeur non null de ses arguments. Utilisez-la pour fournir des valeurs par défaut dans les résultats de requête (COALESCE(title, 'Untitled')) ou pour gérer des colonnes nullables dans des expressions.

Interroger des données JSONB

Le type JSONB de PostgreSQL stocke les données structurées qui ne correspondent pas à un schéma fixe. Les applications IA utilisent généralement JSONB pour les métadonnées, la configuration, les paramètres de modèle et les structures de réponse des variables. PostgreSQL fournit des opérateurs spécialisés pour extraire des valeurs, vérifier la structure et filtrer en fonction du contenu JSON.

Permet -> d’extraire un élément JSON au format JSON ou ->> de l’extraire en tant que texte (pour les comparaisons et l’affichage). Pour les chemins imbriqués, utilisez #> (retourne JSON) ou #>> (retourne du texte). Par exemple, metadata->>'status' extrait le champ d’état en tant que texte, tout en checkpoint_data#>>'{results,0,score}' parcourant un chemin imbriqué pour obtenir une valeur spécifique.

Les opérateurs d’existence et de confinement permettent un filtrage efficace. L’opérateur ? vérifie l’existence de clé (WHERE metadata ? 'priority'), tandis que @> teste l’isolement (WHERE checkpoint_data @> '{"status": "completed"}'). Ces opérateurs peuvent utiliser des index GIN pour filtrer efficacement les tables volumineuses.

Lorsque les colonnes JSONB contiennent des tableaux, utilisez-les jsonb_array_elements pour les développer pour le filtrage ou l’agrégation :

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

Pagination efficace avec la pagination par clé

La pagination traditionnelle basée sur OFFSET devient lente avec les tables volumineuses parce que PostgreSQL doit parcourir et écarter toutes les lignes sautées. La page 1 000 avec 20 lignes par page nécessite l’analyse de 20 000 lignes et l’abandon de 19 980. La pagination d’ensemble de clés (également appelée pagination basée sur le curseur) utilise les clauses WHERE pour ignorer les lignes et cela fonctionne de manière cohérente, quelle que soit la profondeur de la pagination.

Au lieu de suivre les numéros de page, suivez la dernière valeur que vous avez vue et filtrez à partir de là. Cette approche nécessite une colonne unique, triable (ou combinaison de colonnes) :

-- 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’inclusion id dans les deux ORDER BY et WHERE clauses gère les liens lorsque plusieurs lignes ont le même horodatage. Votre application stocke les valeurs de tri de la dernière ligne et les transmet à la requête suivante. Pour l’ordre croissant, passez < à > et DESC à ASC.

Expressions de table communes

Les CTE définissent des jeux de résultats temporaires nommés qui existent uniquement pendant l’exécution d’une requête. Ils améliorent la lisibilité en vous permettant de créer des requêtes complexes étape par étape et d’activer des requêtes récursives pour les données hiérarchiques.

Utilisez des CTEs pour décomposer les requêtes complexes en étapes logiques. Chaque CTE peut référencer des CTEs définies précédemment, en créant un pipeline de transformations :

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;

Les CTE récursives interrogent des structures arborescentes telles que les hiérarchies de tâches, les organigrammes ou les conversations thématiques. Ils se composent d’un cas de base (ancre) et d’un cas récursif qui fait référence à l’objet CTE lui-même. Incluez toujours une limite de profondeur ou une autre condition d’arrêt pour empêcher les boucles infinies si vos données contiennent des cycles :

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;

Les applications IA doivent souvent récupérer des threads de conversation où les messages référencent les messages parents :

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

La clause PostgreSQL RETURNING récupère les valeurs des lignes insérées, mises à jour ou supprimées dans un seul aller-retour. Cela est essentiel pour obtenir des ID générés automatiquement, des horodatages ou des valeurs par défaut calculées sans requête distincte.

-- 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 avec ON CONFLICT

La INSERT ... ON CONFLICT clause gère les violations de contraintes uniques, ce qui active les opérations « upsert » qui insèrent de nouvelles lignes ou mettent à jour des lignes existantes. Ce modèle est utile pour les opérations idempotentes et la gestion de l’état dans les applications IA.

Lorsqu’un conflit se produit sur une contrainte unique, vous pouvez mettre à jour la ligne existante avec de nouvelles valeurs à l’aide de DO UPDATE. La EXCLUDED pseudo-table fait référence aux valeurs qui auraient été insérées :

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;

Utiliser DO NOTHING pour ignorer silencieusement les lignes qui pourraient violer les contraintes : INSERT INTO tags (name) VALUES ('important') ON CONFLICT (name) DO NOTHING.

Vous pouvez ajouter une WHERE clause à DO UPDATE la logique conditionnelle, en mettant à jour uniquement lorsque la nouvelle valeur diffère de celle existante. Combinez ON CONFLICT avec RETURNING pour savoir si une ligne a été insérée ou mise à jour : l’expression (xmax = 0) retourne true les lignes nouvellement insérées et false pour les lignes mises à jour.

Modèles pratiques pour les applications IA

Ces modèles combinent les fonctionnalités décrites précédemment dans les solutions pour les exigences courantes des applications IA. La requête suivante récupère l’historique des conversations avec des métadonnées pour la génération de contexte :

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’exemple suivant montre l’enregistrement des points de contrôle de tâche avec la gestion de l’état à l’aide d’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’exemple suivant montre une recherche paginée avec des filtres 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;

Ressources supplémentaires

Commentaires

Cette page a-t-elle été utile ?