Insertar o actualizar PostgreSQL si existe

Para insertar una fila cuando su clave es nueva y actualizarla cuando la clave ya existe, usa INSERT ... ON CONFLICT (UPSERT) de PostgreSQL. El objetivo del conflicto debe coincidir con una restricción o un índice únicos, como una clave primaria. Por ejemplo, en una tabla users cuya clave primaria es id, INSERT INTO users (id, name, gender, age) VALUES (12, 'John Kane', 'Male', 24) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, gender = EXCLUDED.gender, age = EXCLUDED.age; inserta al usuario 12 o reemplaza esos tres campos en la fila existente.

Los ejemplos siguientes suponen este esquema: CREATE TABLE users (id integer PRIMARY KEY, name text NOT NULL, gender text NOT NULL, age integer NOT NULL);. El SQL se muestra integrado en el texto porque los ejemplos requieren un servidor PostgreSQL; el ejemplo de Python solo representa de forma sencilla la decisión de insertar o actualizar.

Ejecuta este ejemplo:

users = {}

def upsert(user):
    key = user["id"]
    if key in users:
        users[key].update(user)
    else:
        users[key] = user.copy()

upsert({"id": 12, "name": "John Kane", "age": 23})
upsert({"id": 12, "name": "John Kane", "age": 24})
print(users[12])
print("rows:", len(users))

{'id': 12, 'name': 'John Kane', 'age': 24}
rows: 1

Este fragmento de Python solo ilustra la decisión; no se conecta a PostgreSQL ni ejecuta SQL. Los patrones SQL siguientes son ejemplos para la base de datos y deben ejecutarse en una tabla con el esquema indicado. La etiqueta rows: de la salida permanece en inglés porque el bloque de salida no se modifica.

Usar UPDATE y luego INSERT para insertar o actualizar una fila si ya existe

Un UPDATE seguido de un INSERT condicional es un patrón de dos instrucciones. Primero ejecuta UPDATE users SET name = 'John Kane', gender = 'Male', age = 21 WHERE id = 12 RETURNING id;. Si devuelve una fila, se actualizó el usuario existente. Si no devuelve ninguna, ejecuta INSERT INTO users (id, name, gender, age) VALUES (12, 'John Kane', 'Male', 21);.

La aplicación o transacción que ejecuta estas instrucciones debe decidir si realiza la inserción según el resultado de UPDATE ... RETURNING. Un UPDATE y un INSERT separados no forman un UPSERT atómico: otra transacción podría insertar la misma clave entre ambos y provocar un conflicto de clave única. Para una operación normal de inserción o actualización que deba admitir escrituras concurrentes, es preferible usar INSERT ... ON CONFLICT, como se muestra a continuación.

Usar INSERT ... ON CONFLICT (UPSERT) para insertar o actualizar una fila si ya existe

INSERT ... ON CONFLICT intenta realizar la inserción y gestiona el conflicto de unicidad como parte de la misma instrucción. El objetivo del conflicto debe identificar una restricción o un índice únicos válido. En este ejemplo, id es la clave primaria y EXCLUDED hace referencia a la fila propuesta para la inserción.

Usa INSERT INTO users (id, name, gender, age) VALUES (12, 'John Kane', 'Male', 24) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, gender = EXCLUDED.gender, age = EXCLUDED.age; para insertar la fila o reemplazar su nombre, género y edad cuando ya exista id = 12. Si solo deben cambiar determinados campos, enumera únicamente esos campos después de DO UPDATE SET; por ejemplo, DO UPDATE SET age = EXCLUDED.age conserva el nombre y el género existentes.

La acción determina el resultado: una clave nueva inserta la fila proporcionada, mientras que una clave existente actualiza los campos indicados en DO UPDATE SET. También se puede usar DO NOTHING para que un conflicto deje intacta la fila existente. Para consultar información de contexto mantenida por la comunidad, visita la página de UPSERT de PostgreSQL Wiki.

Usar una expresión de tabla común (CTE) para insertar o actualizar una fila si ya existe

Una CTE que modifica datos puede enlazar las filas devueltas por un UPDATE con una inserción condicional. Por ejemplo: WITH updated AS (UPDATE users SET name = 'John Kane', gender = 'Male', age = 25 WHERE id = 12 RETURNING id) INSERT INTO users (id, name, gender, age) SELECT 12, 'John Kane', 'Male', 25 WHERE NOT EXISTS (SELECT 1 FROM updated);.

La inserción se ejecuta solo cuando el UPDATE no devolvió ninguna fila en esa instrucción. Esto puede ser útil cuando la actualización y la inserción alternativa deben formar parte de una sola instrucción SQL, pero no sustituye a ON CONFLICT cuando hay escrituras concurrentes. Una inserción concurrente todavía puede provocar una violación de clave única; por eso, usa INSERT ... ON CONFLICT cuando necesites el comportamiento habitual de UPSERT seguro frente a conflictos.

Usar una función PL/pgSQL para insertar o actualizar una fila si ya existe

Una función PL/pgSQL permite encapsular la instrucción recomendada para gestionar conflictos y reutilizarla. Asigna a los parámetros nombres que no puedan confundirse con los nombres de las columnas. Por ejemplo, el cuerpo de la función puede ejecutar INSERT INTO users (id, name, gender, age) VALUES (p_id, p_name, p_gender, p_age) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, gender = EXCLUDED.gender, age = EXCLUDED.age; con los parámetros p_id integer, p_name text, p_gender text y p_age integer.

Define la función con CREATE OR REPLACE FUNCTION upsert_user(p_id integer, p_name text, p_gender text, p_age integer) RETURNS void LANGUAGE plpgsql AS $$ BEGIN INSERT INTO users (id, name, gender, age) VALUES (p_id, p_name, p_gender, p_age) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, gender = EXCLUDED.gender, age = EXCLUDED.age; END; $$; y luego llámala con SELECT upsert_user(13, 'New User', 'Female', 30);. Así se delega en la base de datos la misma gestión de conflictos de clave única; la función evita tener que recurrir a una secuencia independiente de actualización e inserción que pueda sufrir una condición de carrera.

Conclusión

Para una escritura condicional habitual, usa INSERT ... ON CONFLICT (UPSERT) con una clave primaria u otra restricción única adecuada como objetivo del conflicto. La instrucción inserta una fila nueva y aplica los campos que elijas cuando esa clave ya existe. Un UPDATE seguido de un INSERT y una CTE que combine actualización e inserción pueden ilustrar ambos resultados, pero no ofrecen la misma gestión de conflictos concurrentes. Usa una función PL/pgSQL cuando quieras reutilizar dentro de la base de datos la operación de gestión de conflictos.