# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1527876527 -7200
# Node ID f11366b419ae74d5742ce0d7fdc60709f23f2bcc
# Parent  c38edd879e2e470fa48175d5de96430aa4b6bb73
Fix profile constraints.

diff -r c38edd879e2e -r f11366b419ae wamos.sql
--- a/wamos.sql	Fri Jun 01 19:50:58 2018 +0200
+++ b/wamos.sql	Fri Jun 01 20:08:47 2018 +0200
@@ -196,7 +196,7 @@
 
 CREATE TABLE wamos_waterway.waterway_profiles (
        location isrs NOT NULL,
-       validity tstzrange NOT NULL,
+       validity tstzrange,
        EXCLUDE USING GIST (validity WITH &&),
        PRIMARY KEY (location, validity),
        lnwl smallint,
@@ -204,6 +204,9 @@
        hnwl smallint,
        fe30 smallint,
        fe100 smallint,
+       -- XXX: further normalise using reference_water_levels?
+       CHECK(COALESCE(lnwl, mwl, hnwl, fe30, fe100) IS NULL
+             OR validity IS NOT NULL),
        date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
        source_organization varchar NOT NULL
        );