Mercurial > gemma
annotate schema/updates/1407/01.distinguish_boylat_hydro_ienc.sql @ 5099:3cd736acbad3 queued-stage-done
First version of a reviewed job. I bet it does not work.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Tue, 24 Mar 2020 15:46:37 +0100 |
parents | 58dc06e91c39 |
children |
rev | line source |
---|---|
4964
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
1 CREATE TABLE waterway.fairway_marks_boylat_new ( |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
2 colour varchar, |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
3 colpat varchar, |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
4 conrad int, |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
5 marsys int, |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
6 boyshp int, |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
7 catlam int |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
8 ) INHERITS (waterway.fairway_marks); |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
9 |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
10 CREATE TABLE waterway.fairway_marks_boylat_hydro ( |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
11 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
12 ) INHERITS (waterway.fairway_marks_boylat_new); |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
13 CREATE UNIQUE INDEX fairway_marks_boylat_hydro_distinct_rows |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
14 ON waterway.fairway_marks_boylat_hydro |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
15 ((CAST((validity, last_found, geom, |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
16 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
17 scamin, picrep, txtdsc, sordat, sorind, |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
18 colour, colpat, conrad, marsys, boyshp, catlam, 0 |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
19 ) AS waterway.fairway_marks_boylat_hydro) |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
20 )); |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
21 |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
22 CREATE TABLE waterway.fairway_marks_boylat_ienc ( |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
23 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
24 ) INHERITS (waterway.fairway_marks_boylat_new); |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
25 CREATE UNIQUE INDEX fairway_marks_boylat_ienc_distinct_rows |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
26 ON waterway.fairway_marks_boylat_ienc |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
27 ((CAST((validity, last_found, geom, |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
28 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
29 scamin, picrep, txtdsc, sordat, sorind, |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
30 colour, colpat, conrad, marsys, boyshp, catlam, 0 |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
31 ) AS waterway.fairway_marks_boylat_ienc) |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
32 )); |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
33 |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
34 -- Assume all features are IENC features, since there are currently only |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
35 -- very few features in the data source for HYDRO features |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
36 INSERT INTO waterway.fairway_marks_boylat_ienc |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
37 OVERRIDING USER VALUE |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
38 SELECT validity, last_found, geom, |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
39 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
40 scamin, picrep, txtdsc, sordat, sorind, |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
41 colour, colpat, conrad, marsys, boyshp, catlam, id |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
42 FROM waterway.fairway_marks_boylat; |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
43 |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
44 -- Finally |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
45 DROP TABLE waterway.fairway_marks_boylat; |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
46 ALTER TABLE waterway.fairway_marks_boylat_new RENAME TO fairway_marks_boylat; |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
47 |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
48 GRANT SELECT on ALL tables in schema waterway TO waterway_user; |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
49 GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
50 TO waterway_admin; |