Mercurial > gemma

changeset 2865:cf295096234e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
gemma.sql fixed: Index creation extracted and put after schmema creation
author Thomas Junk <thomas.junk@intevation.de>
date Fri, 29 Mar 2019 12:02:33 +0100
parents e2d362589ce6
children 1fa2c9022362
files schema/gemma.sql
diffstat 1 files changed, 16 insertions(+), 15 deletions(-) [+]
line wrap: on
line diff
--- a/schema/gemma.sql	Fri Mar 29 11:27:01 2019 +0100
+++ b/schema/gemma.sql	Fri Mar 29 12:02:33 2019 +0100
@@ -339,21 +339,6 @@
         source_organization varchar NOT NULL, -- "originator"
         staging_done boolean NOT NULL DEFAULT false
     )
-    -- Constraints are conditional for gauge_measurements, as they
-    -- differ between predicted values and measured ones.  PG does not
-    -- have real conditional unique constraints, but we can use unique
-    -- indeces for that.
-    --
-    -- So we can have a staged and a non-staged
-    -- fk_gauge_id/measure_date pairs in measured values.
-    CREATE UNIQUE INDEX gm_measured_unique_constraint
-        ON gauge_measurements (fk_gauge_id, measure_date, staging_done)
-        WHERE NOT predicted;
-    -- And we can have multiple predictions for one point in time
-    -- (but they are never staged).
-    CREATE UNIQUE INDEX gm_predicted_unique_constraint
-        ON gauge_measurements (fk_gauge_id, measure_date, date_issue)
-        WHERE predicted;
 
     CREATE TABLE waterway_axis (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
@@ -709,6 +694,22 @@
     GROUP BY b.id, g.location, fal.date_info, fal.critical, gmw.water_level;
 ;
 
+-- Constraints are conditional for gauge_measurements, as they
+-- differ between predicted values and measured ones.  PG does not
+-- have real conditional unique constraints, but we can use unique
+-- indeces for that.
+--
+-- So we can have a staged and a non-staged
+-- fk_gauge_id/measure_date pairs in measured values.
+CREATE UNIQUE INDEX gm_measured_unique_constraint
+    ON waterway.gauge_measurements (fk_gauge_id, measure_date, staging_done)
+    WHERE NOT predicted;
+-- And we can have multiple predictions for one point in time
+-- (but they are never staged).
+CREATE UNIQUE INDEX gm_predicted_unique_constraint
+    ON waterway.gauge_measurements (fk_gauge_id, measure_date, date_issue)
+    WHERE predicted;
+
 -- Configure primary keys for geoserver views
 INSERT INTO waterway.gt_pk_metadata VALUES
   ('waterway', 'gauges_geoserver', 'location'),