Mercurial > gemma

diff schema/gemma.sql @ 3648:0ec5c8ec1e44

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Avoid empty validity time ranges An entry which is not valid at any point in time makes no sense. Further, multiple of such entries would violate a UNIQUE constraint. Since an UPDATE now can change validity time ranges, do all the adjustments for that regardles of whether an INSERT or UPDATE happens.
author Tom Gottfried <tom@intevation.de>
date Wed, 12 Jun 2019 18:26:26 +0200
parents 123b9341408e
children 2a079d0a71c1
line wrap: on
line diff
--- a/schema/gemma.sql	Wed Jun 12 18:21:14 2019 +0200
+++ b/schema/gemma.sql	Wed Jun 12 18:26:26 2019 +0200
@@ -280,7 +280,7 @@
         geom geography(POINT, 4326) NOT NULL,
         applicability_from_km int8,
         applicability_to_km int8,
-        validity tstzrange NOT NULL,
+        validity tstzrange NOT NULL CHECK (NOT isempty(validity)),
         zero_point double precision NOT NULL,
         geodref varchar,
         date_info timestamp with time zone NOT NULL,
@@ -288,7 +288,6 @@
         lastupdate timestamp with time zone NOT NULL,
         -- entry removed from external data source (RIS-Index)/historicised:
         erased boolean NOT NULL DEFAULT false,
-        CHECK (erased OR NOT isempty(validity)),
         PRIMARY KEY (location, validity),
         EXCLUDE USING GiST (isrs_astext(location) WITH =, validity WITH &&)
             DEFERRABLE INITIALLY DEFERRED
@@ -489,7 +488,7 @@
     CREATE TABLE bottlenecks (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
         bottleneck_id varchar NOT NULL,
-        validity tstzrange NOT NULL,
+        validity tstzrange NOT NULL CHECK (NOT isempty(validity)),
         EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&)
             DEFERRABLE INITIALLY DEFERRED,
         gauge_location isrs NOT NULL,
@@ -519,7 +518,6 @@
         date_info timestamp with time zone NOT NULL,
         source_organization varchar NOT NULL,
         erased boolean NOT NULL DEFAULT false,
-        CHECK (erased OR NOT isempty(validity)),
         staging_done boolean NOT NULL DEFAULT false
     )
     -- Allow only one non-erased entry per bottleneck