Tengo una tabla que tiene una columna de marca de tiempo algo así como:
Elementos de mesa
id: uuid date: timestamp name: varchar type: varcharMe gustaría poner una restricción única en la tabla que asegure que NO haya dos entradas con el mismo nombre y tipo y que estén dentro de X meses de cualquier otra fila. Tengo procesos asincrónicos que insertan filas en esta tabla, y hacer una selección antes puede fallar debido a las condiciones de carrera.
Puede lograr esto con una "restricción de exclusión", que es como una restricción única generalizada que puede verificar cualquier operador. Consulte esta pregunta para conocer algunos antecedentes sobre la sintaxis.
En particular, podemos decir que no deben existir dos filas A y B donde se cumplen las siguientes condiciones:
A.name es igual a B.nameA.type es igual a B.typeA.date está entre B.date y B.date + 6 months Tenga en cuenta que tampoco necesita verificar los 6 meses antes de B.date, porque eso se verificará mirando las filas al revés: B.date estará entre A.date y A.date + 6 months .
Para que la última condición sea implementable con un solo operador, podemos expresarla en términos de rangos:
A.date a A.date + 6 months se superpone al intervalo B.date a B.date + 6 months Luego podemos escribir una restricción de exclusión que analiza usando el && (superposición de rango), que se ve así:
Alter Table entries Add Constraint name_and_type_within_6_months Exclude Using Gist ( name with =, type with =, tsrange(date, date + interval '6 months') with && );(Sugerencia para Philipe Fatio por esta esencia que muestra una exclusión de rango de fechas ).
Aquí hay una demostración interactiva que muestra esa restricción en acción: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=83181388416d1e5905e088532839ad79
Tu pregunta es un poco vaga. Y lo siguiente no lo responde al 100%, pero puede crear un índice único en una expresión. Por lo tanto, puede evitar que dos filas estén en el mismo mes calendario usando:
create unique index unq_elements_name_type_month on (name, type, date_trunc('month', date)); Los trimestres también serían fáciles, puede usar 'quarter' en lugar de 'month' . Puede extender esto usando aritmética a cualquier número de meses.
Esto no responde exactamente a la pregunta que hiciste. Pero podría ser suficiente para el problema que desea resolver.
Puede agregar un índice único a las dos columnas.
ALTER TABLE elements ADD CONSTRAINT name_type_uidx UNIQUE (name, type) NOT DEFERRABLE;