Mercurial > gemma
changeset 2865:cf295096234e
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'),