Verificar rangos de fechas en PostgreSQL

Mi motor de base de datos favorito para proyectos nuevos es PostgreSQL o postgres. Es una base muy solida, open source, con más de 30 años de desarrollo, se ha ganado una gran reputación por su confiabilidad, robustez y desempeño.

Hoy les quiero compartir un pequeño tip que me ha servido mucho en un par de proyectos, en los cuales se requiere mantener un registro de datos históricos y que no debe haber superposición de un valor en un mismo rango de fechas. Por ejemplo, el valor del IVA generalmente es fijo, pero hubo un tiempo en que cambió. Una tabla para manejar estos cambios en el tiempo sería de la forma:

create table historico_iva(
    id serial,
    empresa_id integer not null,
    desde date not null,
    hasta date null,
    porcentaje_iva numeric(10,2)
)

Esta validación de datos se podría realizar a nivel de aplicación al verificar que no haya 2 valores de IVA para la misma fecha, por ejemplo digamos que el 1 de junio cambia el valor al 10% en la tabla habría los siguientes registros:

Para buscar que valor de iva debo aplicar en una fecha específica puedo usar la consulta:

select porcentaje_iva 
from historico_iva 
where empresa_id = 1 
and current_date between desde and coalesce(hasta, 'Infinity')

Esta debe devolver siempre un solo valor, pero por cualquier error de programación o al editar datos en la base directamente en el registro 2 le pongo 2021-05-31 en la fecha desde, el día 31 de mayo no se sabría si fue 10 o 12 el IVA ya que la consulta devuelve 2 valores. O también que la fecha desde sea mayor a la fecha hasta en cuyo caso nunca retornaría un valor.

Para evitar este inconveniente se puede agregar un par de checks a nivel de base de datos, y para esto postgres ayuda mucho con una extensión para manejo de este tipo de validaciones denominada btree_gist.

Para nuestro ejemplo se debe agregar los siguientes CHECKs en la tabla:

--- activa la extension btree_gist
CREATE EXTENSION btree_gist;

--- verifica que la fecha desde sea menor a la fecha hasta
ALTER TABLE porcentaje_iva 
    ADD CHECK ( "desde" < "hasta");

--- evita superposición de rango de fechas en una misma empresa
ALTER TABLE porcentaje_iva 
    ADD CONSTRAINT no_sobreponer_fechas_iva 
        EXCLUDE USING GIST (
            empresa_id WITH =,
            daterange("desde", coalesce("hasta", 'Infinity'), '[]') WITH &&
        );

Esto implica que al tratar de crear un registro de la misma empresa, que tenga fechas sobrepuestas la base de datos no lo va a permitir y generará un error.