sql - Cómo hacer correctamente el upsert en postgres 9.5

postgresql postgresql-9.5

Question

la sintaxis correcta de upsert con postgresql 9.5, debajo de la consulta muestra la column reference "gallery_id" is ambiguous error column reference "gallery_id" is ambiguous , ¿por qué?

var dbQuery = `INSERT INTO category_gallery (
  category_id, gallery_id, create_date, create_by_user_id
  ) VALUES ($1, $2, $3, $4)
  ON CONFLICT (category_id)
  DO UPDATE SET
  category_id = $1,
  last_modified_date = $3,
  last_modified_by_user_id = $4
  WHERE gallery_id = $2`;

Intenté cambiar WHERE gallery_id = $2; a WHERE category_gallery.gallery_id = $2; luego muestra el error, there is no unique or exclusion constraint matching the ON CONFLICT specification , pero no quiero configurar gallery_id o category_id como único porque quiero asegurarme de que ambas columnas son iguales y luego actualizar ...

¿Cómo hacer correctamente el upsert en postgres 9.5?

Si ON CONFLICT necesita una columna única, ¿debo usar otro método, cómo?

Quiero estar seguro de que tanto la columna múltiple como el conflicto actualicen, cuál es el uso correcto

var dbQuery = `INSERT INTO category_gallery (
  category_id, gallery_id, create_date, create_by_user_id
  ) VALUES ($1, $2, $3, $4)
  ON CONFLICT (category_id, gallery_id)
  DO UPDATE SET
  category_id = $1,
  last_modified_date = $3,
  last_modified_by_user_id = $4
  WHERE gallery_id = $2`;


var dbQuery = `INSERT INTO category_gallery (
  category_id, gallery_id, create_date, create_by_user_id
  ) VALUES ($1, $2, $3, $4)
  ON CONFLICT (category_id AND gallery_id)
  DO UPDATE SET
  category_id = $1,
  last_modified_date = $3,
  last_modified_by_user_id = $4
  WHERE gallery_id = $2`;

tabla (category_id, gallery_id no es una columna única)

category_id | gallery_id | create_date | create_by_user_id | last_modified_date | last_modified_by_user_id
1 | 1 | ...  
1 | 2 | ...
2 | 2 | ...
1 | 3 | ...

Answer 1

Como alternativa simplificada a la respuesta actualmente aceptada , la restricción UNIQUE se puede agregar de forma anónima al crear la tabla:

CREATE TABLE table_name (
    id  TEXT PRIMARY KEY,
    col TEXT,
    UNIQUE (id, col)
);

Entonces, la consulta de inserción se vuelve (similar a lo que ya se respondió):

INSERT INTO table_name (id, col) VALUES ($1, $2)
ON CONFLICT (id, col)
    DO UPDATE SET col = $2;

Answer 2

La construcción ON CONFLICT requiere una restricción UNIQUE para funcionar. De la documentación en la cláusula INSERT .. ON CONFLICT :

La cláusula opcional ON CONFLICT especifica una acción alternativa para generar un error único de violación o restricción de exclusión . Para cada fila individual propuesta para la inserción, ya sea que se realice la inserción o, si se viola una restricción de árbitro o un índice especificado por conflict_target, se toma la acción de conflicto alternativa. ON CONFLICT DO NOTHING simplemente evita insertar una fila como acción alternativa. ON CONFLICT DO UPDATE actualiza la fila existente que entra en conflicto con la fila propuesta para la inserción como acción alternativa.

Ahora, la pregunta no es muy clara, pero probablemente necesite una restricción UNIQUE en las 2 columnas combinadas: (category_id, gallery_id) .

ALTER TABLE category_gallery
    ADD CONSTRAINT category_gallery_uq
    UNIQUE (category_id, gallery_id) ;

Si la fila que se va a insertar coincide con ambos valores con una fila ya en la tabla, en lugar de INSERT , haga una UPDATE :

INSERT INTO category_gallery (
  category_id, gallery_id, create_date, create_by_user_id
  ) VALUES ($1, $2, $3, $4)
  ON CONFLICT (category_id, gallery_id)
  DO UPDATE SET
    last_modified_date = EXCLUDED.create_date,
    last_modified_by_user_id = EXCLUDED.create_by_user_id ;

Puedes usar cualquiera de las columnas de la restricción UNIQUE:

  ON CONFLICT (category_id, gallery_id) 

o el nombre de la restricción:

  ON CONFLICT ON CONSTRAINT category_gallery_uq