Mercurial > gemma

comparison schema/updates/1408/01.distinguish_daymar_hydro_ienc.sql @ 4967:3f704ebad0c5 fairway-marks-import

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