regexp_replace - right postgresql

Use multiple conflict_target en la cláusula ON CONFLICT

Question

Tengo dos columnas en la tabla col1 , col2 , ambas son indexadas únicas (col1 es único y también lo es col2).

Necesito insertar en esta tabla, usar la sintaxis ON CONFLICT y actualizar otras columnas, pero no puedo usar ambas columnas en la cláusula conflict_target .

Funciona:

INSERT INTO table
...
ON CONFLICT ( col1 ) 
DO UPDATE 
SET 
-- update needed columns here

Pero cómo hacer esto para varias columnas, algo como esto:

...
ON CONFLICT ( col1, col2 )
DO UPDATE 
SET 
....

Answer 1

Una tabla de muestra y datos

CREATE TABLE dupes(col1 int primary key, col2 int, col3 text,
   CONSTRAINT col2_unique UNIQUE (col2)
);

INSERT INTO dupes values(1,1,''a''),(2,2,''b'');

Reproduciendo el problema

INSERT INTO dupes values(3,2,''c'')
ON CONFLICT (col1) DO UPDATE SET col3 = ''c'', col2 = 2

Llamemos a esto Q1. El resultado es

ERROR:  duplicate key value violates unique constraint "col2_unique"
DETAIL:  Key (col2)=(2) already exists.

Lo documentation dice la documentation

conflict_target puede realizar una inferencia de índice única. Al realizar la inferencia, consta de una o más columnas index_column_name y / o expresiones index_expression, y un index_predicate opcional. Todos los índices únicos de table_name que, sin importar el orden, contienen exactamente las columnas / expresiones especificadas por conflict_target se infieren (eligen) como índices de árbitro. Si se especifica un index_predicate, debe, como requisito adicional para la inferencia, satisfacer los índices de árbitro.

Esto da la impresión de que la siguiente consulta debería funcionar, pero no lo hace porque en realidad requeriría un índice único en conjunto en col1 y col2. Sin embargo, dicho índice no garantizaría que col1 y col2 serían únicos individualmente, lo cual es uno de los requisitos del OP.

INSERT INTO dupes values(3,2,''c'') 
ON CONFLICT (col1,col2) DO UPDATE SET col3 = ''c'', col2 = 2

Llamemos a esta consulta Q2 (esto falla con un error de sintaxis)

¿Por qué?

Postgresql se comporta de esta manera porque lo que debe suceder cuando ocurre un conflicto en la segunda columna no está bien definido. Hay varias posibilidades. Por ejemplo, en la consulta Q1 anterior, ¿debería postgresql actualizar col1 cuando hay un conflicto en col2 ? Pero, ¿qué pasa si eso lleva a otro conflicto en col1 ? ¿Cómo se espera que Postgresql maneje eso?

Una solución

Una solución es combinar ON CONFLICT con UPSERT tradicional .

CREATE OR REPLACE FUNCTION merge_db(key1 INT, key2 INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE dupes SET col3 = data WHERE col1 = key1 and col2 = key2;
        IF found THEN
            RETURN;
        END IF;

        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently, or key2
        -- already exists in col2,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO dupes VALUES (key1, key2, data) ON CONFLICT (col1) DO UPDATE SET col3 = data;
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            BEGIN
                INSERT INTO dupes VALUES (key1, key2, data) ON CONFLICT (col2) DO UPDATE SET col3 = data;
                RETURN;
            EXCEPTION WHEN unique_violation THEN
                -- Do nothing, and loop to try the UPDATE again.
            END;
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

Debería modificar la lógica de esta función almacenada para que actualice las columnas exactamente de la manera que lo desee. Invocarlo como

SELECT merge_db(3,2,''c'');
SELECT merge_db(1,2,''d'');

Answer 2

1. Cree una restricción (índice externo, por ejemplo).

O Y

2. Mire las restricciones existentes (/ d en psq).
3. Use ON CONSTRAINT (restrict_name) en la cláusula INSERT.

Answer 3

En la actualidad es (parece) imposible. Ni la última versión de la syntax ON CONFLICT permite repetir la cláusula, ni con CTE es posible: no es posible romper el INSERT desde ON CONFLICT para agregar más objetivos de conflicto.

Answer 4

Es una especie de hacky pero resolví esto concatenando los dos valores de col1 y col2 en una nueva columna, col3 (algo así como un índice de los dos) y comparé eso. Esto solo funciona si lo necesita para que coincida AMBOS col1 y col2.

INSERT INTO table
...
ON CONFLICT ( col3 ) 
DO UPDATE 
SET 
-- update needed columns here

Donde col3 = la concatenación de los valores de col1 y col2.

Answer 5

ON CONFLICT es una solución muy torpe, ejecuta

UPDATE dupes SET key1=$1, key2=$2 where key3=$3    
if rowcount > 0    
  INSERT dupes (key1, key2, key3) values ($1,$2,$3);

funciona en Oracle, Postgres y todas las demás bases de datos

Answer 6

Por lo general, podría (creo) generar una declaración con solo una on conflict que especifica la única restricción que es relevante para la cosa que está insertando.

Porque típicamente, solo una restricción es la "relevante", a la vez. (Si son muchos, entonces me pregunto si algo es extraño / extrañamente diseñado, hmm.)

Ejemplo:
_{(Licencia: no CC0, solo CC-By)}

// there''re these unique constraints:
//   unique (site_id, people_id, page_id)
//   unique (site_id, people_id, pages_in_whole_site)
//   unique (site_id, people_id, pages_in_category_id)
// and only *one* of page-id, category-id, whole-site-true/false
// can be specified. So only one constraint is "active", at a time.

val thingColumnName = thingColumnName(notfificationPreference)

val insertStatement = s"""
  insert into page_notf_prefs (
    site_id,
    people_id,
    notf_level,
    page_id,
    pages_in_whole_site,
    pages_in_category_id)
  values (?, ?, ?, ?, ?, ?)
  -- There can be only one on-conflict clause.
  on conflict (site_id, people_id, $thingColumnName)   <—— look
  do update set
    notf_level = excluded.notf_level
  """

val values = List(
  siteId.asAnyRef,
  notfPref.peopleId.asAnyRef,
  notfPref.notfLevel.toInt.asAnyRef,
  // Only one of these is non-null:
  notfPref.pageId.orNullVarchar,
  if (notfPref.wholeSite) true.asAnyRef else NullBoolean,
  notfPref.pagesInCategoryId.orNullInt)

runUpdateSingleRow(insertStatement, values)

Y:

private def thingColumnName(notfPref: PageNotfPref): String =
  if (notfPref.pageId.isDefined)
    "page_id"
  else if (notfPref.pagesInCategoryId.isDefined)
    "pages_in_category_id"
  else if (notfPref.wholeSite)
    "pages_in_whole_site"
  else
    die("TyE2ABK057")

La cláusula on conflict se genera dinámicamente, dependiendo de lo que esté tratando de hacer. Si estoy insertando una preferencia de notificación, para una página, entonces puede haber un conflicto único, en la site_id, people_id, page_id . Y si estoy configurando las preferencias de notificación, para una categoría, entonces sé que la restricción que se puede violar es site_id, people_id, category_id .

Entonces, ¿puedo, y probablemente usted también, en su caso ?, generar el correcto on conflict (... columns ) , porque sé lo que quiero hacer, y luego sé cuál de las restricciones únicas es el que puede ser violado.

Answer 7

Si está utilizando postgres 9.5, puede utilizar el espacio EXCLUIDO.

Ejemplo tomado de Novedades en PostgreSQL 9.5 :

INSERT INTO user_logins (username, logins)
VALUES (''Naomi'',1),(''James'',1)
ON CONFLICT (username)
DO UPDATE SET logins = user_logins.logins + EXCLUDED.logins;

Answer 8

Vlad tuvo la idea correcta.

Primero debe crear una restricción única de tabla en las columnas col1, col2 Luego, una vez que lo haga, puede hacer lo siguiente:

INSERT INTO dupes values(3,2,''c'') 
ON CONFLICT ON CONSTRAINT dupes_pkey 
DO UPDATE SET col3 = ''c'', col2 = 2

Answer 9

ON CONFLICT requiere un índice único * para realizar la detección de conflictos. Entonces solo necesita crear un índice único en ambas columnas:

t=# create table t (id integer, a text, b text);
CREATE TABLE
t=# create unique index idx_t_id_a on t (id, a);
CREATE INDEX
t=# insert into t values (1, ''a'', ''foo'');
INSERT 0 1
t=# insert into t values (1, ''a'', ''bar'') on conflict (id, a) do update set b = ''bar'';
INSERT 0 1
t=# select * from t;
 id | a |  b  
----+---+-----
  1 | a | bar

* Además de los índices únicos, también puede usar restricciones de exclusión . Estos son un poco más generales que las restricciones únicas. Suponga que su tabla tiene columnas para id y valid_time (y valid_time es un tsrange ), y desea permitir id duplicados, pero no para períodos de tiempo superpuestos. Una restricción única no lo ayudará, pero con una restricción de exclusión puede decir "excluir nuevos registros si su id es igual a un id antiguo y también su valid_time superpone a su valid_time ".