Consultar datos

Completado

En esta unidad se tratan las características y patrones de consulta específicos de PostgreSQL que son esenciales para las aplicaciones de inteligencia artificial. La unidad se centra en las funcionalidades únicas de PostgreSQL y los patrones avanzados para administrar el historial de conversaciones, el estado de procesamiento y los metadatos de la aplicación.

En esta unidad se asume que está familiarizado con los conceptos estándar de SQL, como SELECT, WHERE, combinaciones y agregación. Si necesita un repaso, el itinerario de aprendizaje Introducción a la consulta con Transact-SQL es un buen recurso.

Ejecución de consultas y ámbito de alias

Incluso los desarrolladores de SQL experimentados tropiezan con el orden de ejecución, especialmente cuando se usan alias de columna. Las instrucciones SQL se ejecutan en un orden lógico específico que difiere de la forma de escribirlas, y el hecho de comprender este orden le ayuda a resolver errores confusos de "columna no existe".

Pedido de	Cláusula	Propósito
1	FROM	Identificación de tablas de origen
2	WHERE	Filtrar filas
3	GROUP BY	Agrupar filas para la agregación
4	HAVING	Grupos de filtros
5	SELECT	Seleccione columnas y calcule expresiones
6	ORDER BY	Ordenar resultados
7	LIMIT / OFFSET	Restricción del recuento de resultados

Regla de ámbito de alias: Los alias de columna definidos en SELECT solo son visibles para las cláusulas que se ejecutan despuésSELECT, es decir ORDER BY , y LIMIT/OFFSET. Las cláusulas que se ejecutan anteriormente (WHERE, GROUP BY, HAVING) no pueden hacer referencia a estos alias porque aún no están definidos.

Esto significa que no puede filtrar por un alias en WHERE, pero puede ordenar por uno en 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;

Filtrado específico de PostgreSQL

PostgreSQL amplía SQL estándar con operadores que simplifican las tareas comunes de filtrado. Estas características son útiles para las aplicaciones de inteligencia artificial que necesitan consultas de metadatos estructurados y búsqueda de texto flexible.

El operador ILIKE proporciona coincidencias de patrones que no distinguen mayúsculas de minúsculas sin necesidad de funciones como LOWER(). Esto es útil para las características de búsqueda orientadas al usuario en las que se desea hacer obtener coincidencias independientemente de las mayúsculas y minúsculas/: SELECT * FROM messages WHERE content ILIKE '%error%'.

PostgreSQL permite controlar dónde los valores NULL aparecen en los resultados ordenados mediante NULLS FIRST o NULLS LAST. De forma predeterminada, los NULL valores ordenan como si fuera mayor que cualquier otro valor. Use ORDER BY ended_at NULLS LAST para mantener conversaciones incompletas al final o ORDER BY completed_at NULLS FIRST para mostrar primero las tareas sin procesar.

La COALESCE función devuelve el primer valor distinto de NULL de sus argumentos. Úselo para proporcionar valores predeterminados en los resultados de la consulta (COALESCE(title, 'Untitled')) o para controlar columnas que aceptan valores NULL en expresiones.

Consulta de datos JSONB

El tipo JSONB de PostgreSQL almacena datos estructurados que no se ajustan a un esquema fijo. Las aplicaciones de inteligencia artificial suelen usar JSONB para metadatos, configuración, parámetros de modelo y estructuras de respuesta variable. PostgreSQL proporciona operadores especializados para extraer valores, comprobar la estructura y filtrar en función del contenido JSON.

Use -> para extraer un elemento JSON como JSON o ->> para extraerlo como texto (para comparaciones y mostrar). Para rutas anidadas, use #> (devuelve JSON) o #>> (devuelve texto). Por ejemplo, metadata->>'status' extrae el campo de estado como texto, mientras checkpoint_data#>>'{results,0,score}' navega por una ruta anidada para obtener un valor específico.

Los operadores de existencia y contención permiten un filtrado eficaz. El ? operador comprueba la existencia de claves (WHERE metadata ? 'priority'), mientras que @> verifica la contención (WHERE checkpoint_data @> '{"status": "completed"}'). Estos operadores pueden usar índices GIN para un filtrado eficaz en tablas grandes.

Cuando las columnas JSONB contienen matrices, use jsonb_array_elements para expandirlas para filtrarlas o agregarlas:

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

Paginación eficaz con paginación por conjuntos de claves

La paginación basada en OFFSET tradicional se ralentiza en tablas grandes porque PostgreSQL debe examinar y descartar todas las filas omitidas. La página 1000 con 20 filas por página requiere examinar 20 000 filas y descartar 19 980. La paginación del conjunto de claves (también denominada paginación basada en cursor) utiliza las cláusulas WHERE para omitir filas, lo cual funciona de manera consistente, independientemente de la profundidad de la paginación.

En lugar de realizar un seguimiento de los números de página, realice un seguimiento del último valor que vio y filtre desde allí. Este enfoque requiere una columna única y ordenada (o combinación de columnas):

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

La inclusión de id en ambas cláusulas ORDER BY y WHERE maneja los empates cuando varias filas tienen la misma marca de tiempo. La aplicación almacena los valores de ordenación de la última fila y los pasa a la siguiente consulta. Para el orden ascendente, cambie < a > y DESC a ASC.

Expresiones de tabla común (CTE)

Los CTE definen conjuntos de resultados temporales con nombre que solo existen durante la ejecución de una consulta. Mejoran la legibilidad al permitirle crear consultas complejas paso a paso y habilitan consultas recursivas para datos jerárquicos.

Use CTE para dividir consultas complejas en pasos lógicos. Cada CTE puede hacer referencia a los CTEs definidos previamente, creando una cadena de transformaciones.

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;

Las CTE recursivas se utilizan para consultar estructuras de árbol, como jerarquías de tareas, organigramas o conversaciones hiladas. Constan de un caso base (delimitador) y un caso recursivo que hace referencia al propio CTE. Incluya siempre un límite de profundidad u otra condición de terminación para evitar bucles infinitos si los datos contienen ciclos:

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;

A menudo, las aplicaciones de inteligencia artificial necesitan recuperar subprocesos de conversación en los que los mensajes hacen referencia a mensajes primarios:

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 cláusula RETURNING de PostgreSQL recupera valores de filas insertadas, actualizadas o eliminadas en un solo paso. Esto es esencial para obtener identificadores generados automáticamente, marcas de tiempo o valores predeterminados calculados sin una consulta independiente.

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

La cláusula INSERT ... ON CONFLICT maneja violaciones de restricciones únicas, permitiendo operaciones "upsert" que insertan nuevas filas o actualizan las existentes. Este patrón es útil para las operaciones idempotentes y la administración de estados en las aplicaciones de IA.

Cuando se produce un conflicto en una restricción única, puede actualizar la fila existente con nuevos valores mediante DO UPDATE. La EXCLUDED pseudo-tabla hace referencia a los valores que se habrían insertado:

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;

Use DO NOTHING para omitir de forma silenciosa las filas que infringirían restricciones: INSERT INTO tags (name) VALUES ('important') ON CONFLICT (name) DO NOTHING.

Puede agregar una WHERE cláusula a DO UPDATE para la lógica condicional, actualizando solo cuando el nuevo valor difiere del existente. Combine ON CONFLICT con RETURNING para saber si se insertó o actualizó una fila: la expresión (xmax = 0) devuelve true para las filas recién insertadas y false para las filas actualizadas.

Patrones prácticos para aplicaciones de inteligencia artificial

Estos patrones combinan las características que se trataron anteriormente en soluciones para los requisitos comunes de la aplicación de IA. La consulta siguiente recupera el historial de conversaciones con metadatos para la creación de contexto:

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;

En el ejemplo siguiente se muestran los puntos de control de tareas de grabación con administración de estado mediante 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;

En el ejemplo siguiente se muestra una búsqueda paginada con filtros 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;

Recursos adicionales

• Funciones y operadores JSON de PostgreSQL
• Documentación de PostgreSQL INSERT
• Consultas WITH de PostgreSQL (CTE)