Mercurial > gemma
changeset 2866:1fa2c9022362
merge
author | Thomas Junk <thomas.junk@intevation.de> |
---|---|
date | Fri, 29 Mar 2019 12:07:00 +0100 |
parents | cf295096234e |
children | 23ff7f6c543b |
files | schema/gemma.sql |
diffstat | 1 files changed, 15 insertions(+), 16 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Fri Mar 29 12:02:33 2019 +0100 +++ b/schema/gemma.sql Fri Mar 29 12:07:00 2019 +0100 @@ -339,6 +339,21 @@ 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, @@ -694,22 +709,6 @@ 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'),