Mercurial > gemma
view schema/updates/1408/01.distinguish_daymar_hydro_ienc.sql @ 5677:a57be8bfb6ea sr-v2
More suited file names.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Sun, 11 Feb 2024 09:51:21 +0100 |
parents | 3f704ebad0c5 |
children |
line wrap: on
line source
CREATE TABLE waterway.fairway_marks_daymar_new ( colour varchar, colpat varchar, condtn int, topshp int ) INHERITS (waterway.fairway_marks); CREATE TABLE waterway.fairway_marks_daymar_hydro ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY ) INHERITS (waterway.fairway_marks_daymar_new); CREATE UNIQUE INDEX fairway_marks_daymar_hydro_distinct_rows ON waterway.fairway_marks_daymar_hydro ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, colour, colpat, condtn, topshp, 0 ) AS waterway.fairway_marks_daymar_hydro) )); CREATE TABLE waterway.fairway_marks_daymar_ienc ( orient double precision, id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY ) INHERITS (waterway.fairway_marks_daymar_new); CREATE UNIQUE INDEX fairway_marks_daymar_ienc_distinct_rows ON waterway.fairway_marks_daymar_ienc ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, colour, colpat, condtn, topshp, orient, 0 ) AS waterway.fairway_marks_daymar_ienc) )); -- Assume all features not being definitely IENC features are HYDRO features INSERT INTO waterway.fairway_marks_daymar_hydro OVERRIDING USER VALUE SELECT validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, colour, colpat, condtn, topshp, id FROM waterway.fairway_marks_daymar WHERE orient IS NULL AND id NOT IN( SELECT fm_daymar_id FROM waterway.fairway_marks_daymar_dirimps); INSERT INTO waterway.fairway_marks_daymar_ienc SELECT validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, colour, colpat, condtn, topshp, orient, id FROM waterway.fairway_marks_daymar WHERE orient IS NOT NULL OR id IN( SELECT fm_daymar_id FROM waterway.fairway_marks_daymar_dirimps); SELECT setval( pg_get_serial_sequence('waterway.fairway_marks_daymar_ienc', 'id'), max(id)) FROM waterway.fairway_marks_daymar_ienc; -- Let foreign key constraint point to new table ALTER TABLE waterway.fairway_marks_daymar_dirimps DROP CONSTRAINT fairway_marks_daymar_dirimps_fm_daymar_id_fkey, ADD FOREIGN KEY (fm_daymar_id) REFERENCES waterway.fairway_marks_daymar_ienc; -- Finally DROP TABLE waterway.fairway_marks_daymar; ALTER TABLE waterway.fairway_marks_daymar_new RENAME TO fairway_marks_daymar; GRANT SELECT on ALL tables in schema waterway TO waterway_user; GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway TO waterway_admin;