es.davy.ai

Preguntas y respuestas de programación confiables

¿Tienes una pregunta?

Si tienes alguna pregunta, puedes hacerla a continuación o ingresar lo que estás buscando.

Upsert de Postgresql basado en condición.

06 Aug, 2023 Programación

Tengo las siguientes tablas

CREATE TABLE users (
   id UUID PRIMARY KEY DEFAULT uuid_generate_v4 (),
   ...

CREATE TABLE tags (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4 (),
    user_id UUID NOT NULL references users (id),
    tag VARCHAR(200) NOT NULL,
...
}

Me gustaría formar una consulta que inserte una etiqueta basada en las siguientes restricciones:
1. Para un user_id dado en la tabla tags, todas las entradas deben tener etiquetas únicas.
2. Diferentes user_id pueden tener la misma etiqueta. Por ejemplo:

La siguiente tabla debería ser válida en la tabla tag

id user_id tag
some-tag-uuid-1 some-user-uuid-1 foo
some-tag-uuid-2 some-user-uuid-1 bar
some-tag-uuid-3 some-user-uuid-2 foo

Fíjate en las diferencias en el user_id.

La siguiente tabla NO debería ser válida en la tabla tag

id user_id tag
some-tag-uuid-1 some-user-uuid-1 foo
some-tag-uuid-2 some-user-uuid-1 foo
  1. Si una entrada existe, debo devolver el id de la etiqueta existente. Si no existe, insertamos la nueva etiqueta y devolvemos el id de la nueva etiqueta.

Lo que tengo actualmente

Por ahora, la única consulta que se me ocurre se divide en dos partes y la aplicación maneja la lógica intermedia.

Para una etiqueta dada para insertar, por ejemplo:

{id: 'some-tag-uuid-1', user_id: 'some-user-uuid-1', tag: 'busy'};

SELECT id FROM tag WHERE user_id = 'some-user-uuid-1' AND tag = 'busy'

A partir de las filas resultantes, luego compruebo si existe. Si es así, devuelvo el id existente. Si no, inserto el nuevo id en la tabla de etiquetas y devuelvo el nuevo id.

No estoy seguro si este enfoque es el mejor y me gustaría tener una sola consulta más eficiente (si es posible).

Tags:  ,

Answer

  1. Avatar for davy.ai
    davy.ai

    Para insertar una nueva etiqueta y devolver el ID de la etiqueta existente o recién creada, se puede utilizar la sintaxis INSERT ... ON CONFLICT ... RETURNING en PostgreSQL. Aquí tienes un ejemplo de consulta que debería lograr la funcionalidad deseada:

    INSERT INTO tags (id, user_id, tag)
VALUES ('some-tag-uuid-1', 'some-user-uuid-1', 'busy')
ON CONFLICT (user_id, tag) DO UPDATE SET id = excluded.id
RETURNING id;

    Explicación:

    • La instrucción INSERT INTO ... VALUES especifica los valores a insertar en la tabla tags.
    • La cláusula ON CONFLICT especifica qué hacer si la inserción entra en conflicto con una fila existente. En este caso, especificamos una restricción única en las columnas user_id y tag, por lo que cualquier fila en conflicto tendrá esos valores.
    • La cláusula DO UPDATE SET le dice a PostgreSQL cómo actualizar la fila en conflicto para resolver el conflicto. En este caso, simplemente actualizamos la columna id con el valor excluded.id (que es el valor id que intentamos insertar).
    • Por último, la cláusula RETURNING especifica qué valores devolver de la consulta. En este caso, devolvemos el id tanto de la fila existente como de la fila recién insertada.

    Nota: Asegúrate de tener instalada la extensión uuid-ossp en tu base de datos de PostgreSQL para poder utilizar la función uuid_generate_v4() para generar UUIDs. Puedes comprobar si la extensión está instalada ejecutando SELECT * FROM pg_extension WHERE extname = 'uuid-ossp';. Si no está instalada, puedes instalarla ejecutando CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; como superusuario.

Comments are closed.