Mercurial > gemma

annotate schema/updates/1426/01.historicise_axis.sql @ 5445:0d6fb393c79a marking-single-beam

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged default into marking-single-beam branch.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 14 Jul 2021 17:46:30 +0200
parents e8661379a6c5
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
5016
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 ALTER TABLE waterway.waterway_axis
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 ADD validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL)
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 CHECK (NOT isempty(validity)),
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 ADD last_found timestamp with time zone NOT NULL DEFAULT current_timestamp;
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5
5022
e8661379a6c5 Be a bit less stupid in initialising axis validity
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
6 -- Assume existing entries have been valid since last successful import
5016
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 UPDATE waterway.waterway_axis SET validity = tstzrange(
5022
e8661379a6c5 Be a bit less stupid in initialising axis validity
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
8 (SELECT max(changed) FROM import.imports
e8661379a6c5 Be a bit less stupid in initialising axis validity
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
9 WHERE kind = 'wx' AND state = CAST('accepted' AS import_state)),
5016
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 NULL);
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 DROP TRIGGER waterway_axis_wtwaxs_unique ON waterway.waterway_axis;
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 CREATE CONSTRAINT TRIGGER waterway_axis_wtwaxs_unique
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 AFTER INSERT OR UPDATE OF wtwaxs, validity ON waterway.waterway_axis
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('wtwaxs', 'validity');
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 CREATE INDEX waterway_axis_validity
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 ON waterway.waterway_axis USING GiST (validity);
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 -- No more need to delete
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 DROP POLICY responsibility_area_delete ON waterway.waterway_axis;