Upsert es una combinación de update e insert. El upsert permite actualizar una fila existente o insertar una nueva si no existe.

PostgreSQL no tiene la sentencia UPSERT pero soporta la operación upsert a través de la sentencia INSERT...ON CONFLICT.

Esta es la sintaxis básica de la sentencia INSERT...ON CONFLICT:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_column)
DO NOTHING | DO UPDATE SET column1 = value1, column2 = value2, ...;Code language: SQL (Structured Query Language) (sql)

En esta sintaxis:

nombre_tabla: Es el nombre de la tabla donde se quieren insertar los datos.
(columna1, columna2, ...): La lista de columnas para las que se proporcionan valores para insertar en la tabla.
VALORES(valor1, valor2, ...): Los valores que desea insertar en las columnas especificadas (columna1 , columna2, ...).
ON CONFLICTO (columna_conflicto): Esta cláusula especifica el objetivo del conflicto, que es la restricción única o el índice único que puede causar un conflicto.
DO NOTHING: Indica a PostgreSQL que no haga nada cuando se produzca un conflicto.
DO UPDATE: Realiza una actualización si se produce un conflicto.
SET columna = valor1, columna = valor2, ...: Esto lista las columnas a actualizar y sus correspondientes valores en caso de conflicto.

Cómo funciona la sentencia INSERT ... ON CONFLICT.

En primer lugar, la cláusula ON CONFLICT identifica el objetivo del conflicto, que suele ser una restricción única (o un índice único). Si los datos que inserta violan la restricción, se produce un conflicto.

En segundo lugar, la cláusula DO UPDATE indica a PostgreSQL que actualice las filas existentes o que no haga nada en lugar de abortar toda la operación cuando se produce un conflicto.

Tercero, la cláusula SET define las columnas y valores a actualizar. Puede utilizar nuevos valores o hacer referencia a los valores que intentó insertar utilizando la palabra clave EXCLUDED.

Ejemplos de UPSERT en PostgreSQL¶

Las siguientes sentencias crean la tabla inventario e insertan datos en ella:

CREATE TABLE inventory(
   id INT PRIMARY KEY,
   name VARCHAR(255) NOT NULL,
   price DECIMAL(10,2) NOT NULL,
   quantity INT NOT NULL
);

INSERT INTO inventory(id, name, price, quantity)
VALUES
    (1, 'A', 15.99, 100),
    (2, 'B', 25.49, 50),
    (3, 'C', 19.95, 75)
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Salida:

 id | name | price | quantity
----+------+-------+----------
  1 | A    | 15.99 |      100
  2 | B    | 25.49 |       50
  3 | C    | 19.95 |       75
(3 rows)


INSERT 0 3

La tabla de inventario contiene información sobre varios productos, incluidos sus nombres, precios y cantidades en stock.

Supongamos que recibe una lista actualizada de productos con nuevos precios y necesita actualizar el inventario en consecuencia.

En este caso, la operación upsert puede ser útil para manejar las siguientes situaciones:

Actualización de productos existentes. Si un producto ya existe en la tabla de inventario, desea actualizar su precio y cantidad con la nueva información.
Insertar nuevos productos. Si un producto no está en la tabla de inventario, desea insertarlo en la tabla.

Ejemplo básico de sentencia PostgreSQL INSERT ... ON CONFLICT¶

El siguiente ejemplo utiliza la sentencia INSERT .. . ON CONFLICT para insertar una nueva fila en la tabla de inventario:

INSERT INTO inventory (id, name, price, quantity)
VALUES (1, 'A', 16.99, 120)
ON CONFLICT(id) 
DO UPDATE SET
  price = EXCLUDED.price,
  quantity = EXCLUDED.quantity;Code language: SQL (Structured Query Language) (sql)

Salida:

INSERT 0 1

En este ejemplo, intentamos insertar una nueva fila en la tabla de inventario.

Sin embargo, la tabla de inventario ya tiene una fila con id 1, por lo tanto, se produce un conflicto.

El DO UPDATE cambia el precio y la cantidad del producto a los nuevos valores que se están insertando. El EXCLUIDO permite acceder a los nuevos valores.

La siguiente sentencia verifica la actualización:

SELECT * FROM inventory 
WHERE id = 1;Code language: SQL (Structured Query Language) (sql)

Salida:

 id | name | price | quantity
----+------+-------+----------
  1 | A    | 16.99 |      120
(1 row)

Ejemplo de inserción de datos¶

El siguiente ejemplo utiliza la sentencia INSERT ... ON CONFLICT para insertar una nueva fila en la tabla de inventario:

INSERT INTO inventory (id, name, price, quantity)
VALUES (4, 'D', 29.99, 20)
ON CONFLICT(id) 
DO UPDATE SET
  price = EXCLUDED.price,
  quantity = EXCLUDED.quantity;Code language: SQL (Structured Query Language) (sql)

Salida:

INSERT 0 1

En este caso, la sentencia inserta una nueva fila en la tabla de inventario porque el id de producto 4 no existe en la tabla de inventario.

La siguiente sentencia verifica la inserción:

SELECT * FROM inventory
ORDER BY id;Code language: SQL (Structured Query Language) (sql)

Salida:

 id | name | price | quantity
----+------+-------+----------
  1 | A    | 16.99 |      120
  2 | B    | 25.49 |       50
  3 | C    | 19.95 |       75
  4 | D    | 29.99 |       20
(4 rows)

Resumen¶
Use el upsert de PostgreSQL para actualizar datos si ya existen o insertar los datos si no existen.
Utilice la sentencia INSERT ON CONFLICT para upsert.

Ejemplos de UPSERT en PostgreSQL¶

Ejemplo básico de sentencia PostgreSQL INSERT ... ON CONFLICT¶

Ejemplo de inserción de datos¶

Resumen¶