Mercurial > gemma
annotate schema/updates/1406/01.distinguish_bcnlat_hydro_ienc.sql @ 4998:bb2123358bd8 wmst-config
Configure fairway marks layers with time support
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Mon, 09 Mar 2020 12:19:06 +0100 |
parents | 1e47ba2a58f2 |
children |
rev | line source |
---|---|
4962
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
1 CREATE TABLE waterway.fairway_marks_bcnlat_new ( |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
2 colour varchar, |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
3 colpat varchar, |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
4 condtn int, |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
5 bcnshp int, |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
6 catlam int |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
7 ) INHERITS (waterway.fairway_marks); |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
8 |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
9 CREATE TABLE waterway.fairway_marks_bcnlat_hydro ( |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
10 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
11 ) INHERITS (waterway.fairway_marks_bcnlat_new); |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
12 CREATE UNIQUE INDEX fairway_marks_bcnlat_hydro_distinct_rows |
4963 | 13 ON waterway.fairway_marks_bcnlat_hydro |
4962
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
14 ((CAST((validity, last_found, geom, |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
15 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
16 scamin, picrep, txtdsc, sordat, sorind, |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
17 colour, colpat, condtn, bcnshp, catlam, 0 |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
18 ) AS waterway.fairway_marks_bcnlat_hydro) |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
19 )); |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
20 |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
21 CREATE TABLE waterway.fairway_marks_bcnlat_ienc ( |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
22 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
23 ) INHERITS (waterway.fairway_marks_bcnlat_new); |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
24 CREATE UNIQUE INDEX fairway_marks_bcnlat_ienc_distinct_rows |
4963 | 25 ON waterway.fairway_marks_bcnlat_ienc |
4962
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
26 ((CAST((validity, last_found, geom, |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
27 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
28 scamin, picrep, txtdsc, sordat, sorind, |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
29 colour, colpat, condtn, bcnshp, catlam, 0 |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
30 ) AS waterway.fairway_marks_bcnlat_ienc) |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
31 )); |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
32 |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
33 -- Assume all features are IENC features, since there is currently no known |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
34 -- data source for HYDRO features |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
35 INSERT INTO waterway.fairway_marks_bcnlat_ienc |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
36 SELECT validity, last_found, geom, |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
37 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
38 scamin, picrep, txtdsc, sordat, sorind, |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
39 colour, colpat, condtn, bcnshp, catlam, id |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
40 FROM waterway.fairway_marks_bcnlat; |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
41 SELECT setval( |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
42 pg_get_serial_sequence('waterway.fairway_marks_bcnlat_ienc', 'id'), |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
43 max(id)) |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
44 FROM waterway.fairway_marks_bcnlat_ienc; |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
45 |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
46 -- Let foreign key constraint point to new table |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
47 ALTER TABLE waterway.fairway_marks_bcnlat_dirimps |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
48 DROP CONSTRAINT fairway_marks_bcnlat_dirimps_fm_bcnlat_id_fkey, |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
49 ADD FOREIGN KEY (fm_bcnlat_id) |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
50 REFERENCES waterway.fairway_marks_bcnlat_ienc; |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
51 |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
52 -- Finally |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
53 DROP TABLE waterway.fairway_marks_bcnlat; |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
54 ALTER TABLE waterway.fairway_marks_bcnlat_new RENAME TO fairway_marks_bcnlat; |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
55 |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
56 GRANT SELECT on ALL tables in schema waterway TO waterway_user; |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
57 GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
58 TO waterway_admin; |