Mercurial > gemma
view schema/updates/1406/01.distinguish_bcnlat_hydro_ienc.sql @ 5560:f2204f91d286
Join the log lines of imports to the log exports to recover data from them.
Used in SR export to extract information that where in the meta json
but now are only found in the log.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 09 Feb 2022 18:34:40 +0100 |
parents | 1e47ba2a58f2 |
children |
line wrap: on
line source
CREATE TABLE waterway.fairway_marks_bcnlat_new ( colour varchar, colpat varchar, condtn int, bcnshp int, catlam int ) INHERITS (waterway.fairway_marks); CREATE TABLE waterway.fairway_marks_bcnlat_hydro ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY ) INHERITS (waterway.fairway_marks_bcnlat_new); CREATE UNIQUE INDEX fairway_marks_bcnlat_hydro_distinct_rows ON waterway.fairway_marks_bcnlat_hydro ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, colour, colpat, condtn, bcnshp, catlam, 0 ) AS waterway.fairway_marks_bcnlat_hydro) )); CREATE TABLE waterway.fairway_marks_bcnlat_ienc ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY ) INHERITS (waterway.fairway_marks_bcnlat_new); CREATE UNIQUE INDEX fairway_marks_bcnlat_ienc_distinct_rows ON waterway.fairway_marks_bcnlat_ienc ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, colour, colpat, condtn, bcnshp, catlam, 0 ) AS waterway.fairway_marks_bcnlat_ienc) )); -- Assume all features are IENC features, since there is currently no known -- data source for HYDRO features INSERT INTO waterway.fairway_marks_bcnlat_ienc SELECT validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, colour, colpat, condtn, bcnshp, catlam, id FROM waterway.fairway_marks_bcnlat; SELECT setval( pg_get_serial_sequence('waterway.fairway_marks_bcnlat_ienc', 'id'), max(id)) FROM waterway.fairway_marks_bcnlat_ienc; -- Let foreign key constraint point to new table ALTER TABLE waterway.fairway_marks_bcnlat_dirimps DROP CONSTRAINT fairway_marks_bcnlat_dirimps_fm_bcnlat_id_fkey, ADD FOREIGN KEY (fm_bcnlat_id) REFERENCES waterway.fairway_marks_bcnlat_ienc; -- Finally DROP TABLE waterway.fairway_marks_bcnlat; ALTER TABLE waterway.fairway_marks_bcnlat_new RENAME TO fairway_marks_bcnlat; GRANT SELECT on ALL tables in schema waterway TO waterway_user; GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway TO waterway_admin;