Mercurial > gemma

annotate schema/updates/1438/01.remove_duplicate_fairway_areas.sql @ 5155:20f338cb4d48

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add forgotten cleanup of fairway dimensions This is actually a fixup of rev. e21cbb9768a2, that added database schema version 1431 with a constraint trigger preventing new or updated rows to have a duplicate area but missed to cleanup existing data. So cleanup now, before the duplicates hurt in future imports that try to keep the duplicates in history.
author Tom Gottfried <tom@intevation.de>
date Wed, 01 Apr 2020 19:01:08 +0200
parents
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
5155
20f338cb4d48 Add forgotten cleanup of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 -- Remove entries that do not suffice the requirements set out by the
20f338cb4d48 Add forgotten cleanup of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 -- constraint trigger on fairway_dimensions, but that could be left over
20f338cb4d48 Add forgotten cleanup of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 -- from an import run before the constraint trigger was in place
20f338cb4d48 Add forgotten cleanup of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 DELETE FROM waterway.fairway_dimensions fd
20f338cb4d48 Add forgotten cleanup of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 USING waterway.fairway_dimensions fd_self
20f338cb4d48 Add forgotten cleanup of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 WHERE ST_Equals(CAST(fd.area AS geometry), CAST(fd_self.area AS geometry))
20f338cb4d48 Add forgotten cleanup of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 AND fd.validity && fd_self.validity
20f338cb4d48 Add forgotten cleanup of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 AND fd.staging_done = fd_self.staging_done
20f338cb4d48 Add forgotten cleanup of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 AND fd.id < fd_self.id