Mercurial > gemma

annotate schema/updates/1408/01.distinguish_daymar_hydro_ienc.sql @ 5666:37c2354a6024 clickable-links

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Render links only to known bottlenecks
author Thomas Junk <thomas.junk@intevation.de>
date Tue, 05 Dec 2023 15:34:31 +0100
parents 3f704ebad0c5
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4967
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 CREATE TABLE waterway.fairway_marks_daymar_new (
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 colour varchar,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 colpat varchar,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 condtn int,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 topshp int
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 ) INHERITS (waterway.fairway_marks);
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 CREATE TABLE waterway.fairway_marks_daymar_hydro (
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 ) INHERITS (waterway.fairway_marks_daymar_new);
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 CREATE UNIQUE INDEX fairway_marks_daymar_hydro_distinct_rows
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 ON waterway.fairway_marks_daymar_hydro
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 ((CAST((validity, last_found, geom,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 scamin, picrep, txtdsc, sordat, sorind,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 colour, colpat, condtn, topshp, 0
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 ) AS waterway.fairway_marks_daymar_hydro)
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 ));
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 CREATE TABLE waterway.fairway_marks_daymar_ienc (
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 orient double precision,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 ) INHERITS (waterway.fairway_marks_daymar_new);
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 CREATE UNIQUE INDEX fairway_marks_daymar_ienc_distinct_rows
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 ON waterway.fairway_marks_daymar_ienc
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 ((CAST((validity, last_found, geom,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 scamin, picrep, txtdsc, sordat, sorind,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 colour, colpat, condtn, topshp, orient, 0
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 ) AS waterway.fairway_marks_daymar_ienc)
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 ));
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 -- Assume all features not being definitely IENC features are HYDRO features
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 INSERT INTO waterway.fairway_marks_daymar_hydro
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 OVERRIDING USER VALUE
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 SELECT validity, last_found, geom,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 scamin, picrep, txtdsc, sordat, sorind,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 colour, colpat, condtn, topshp, id
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 FROM waterway.fairway_marks_daymar
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 WHERE orient IS NULL AND id NOT IN(
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 SELECT fm_daymar_id FROM waterway.fairway_marks_daymar_dirimps);
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 INSERT INTO waterway.fairway_marks_daymar_ienc
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 SELECT validity, last_found, geom,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 scamin, picrep, txtdsc, sordat, sorind,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 colour, colpat, condtn, topshp, orient, id
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 FROM waterway.fairway_marks_daymar
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 WHERE orient IS NOT NULL OR id IN(
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 SELECT fm_daymar_id FROM waterway.fairway_marks_daymar_dirimps);
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 SELECT setval(
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53 pg_get_serial_sequence('waterway.fairway_marks_daymar_ienc', 'id'),
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 max(id))
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 FROM waterway.fairway_marks_daymar_ienc;
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 -- Let foreign key constraint point to new table
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 ALTER TABLE waterway.fairway_marks_daymar_dirimps
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59 DROP CONSTRAINT fairway_marks_daymar_dirimps_fm_daymar_id_fkey,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 ADD FOREIGN KEY (fm_daymar_id)
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 REFERENCES waterway.fairway_marks_daymar_ienc;
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 -- Finally
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 DROP TABLE waterway.fairway_marks_daymar;
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65 ALTER TABLE waterway.fairway_marks_daymar_new RENAME TO fairway_marks_daymar;
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
67 GRANT SELECT on ALL tables in schema waterway TO waterway_user;
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 TO waterway_admin;