CREATE TABLE waterway.fairway_marks_boylat_new (
    colour varchar,
    colpat varchar,
    conrad int,
    marsys int,
    boyshp int,
    catlam int
) INHERITS (waterway.fairway_marks);

CREATE TABLE waterway.fairway_marks_boylat_hydro (
    id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
) INHERITS (waterway.fairway_marks_boylat_new);
CREATE UNIQUE INDEX fairway_marks_boylat_hydro_distinct_rows
    ON waterway.fairway_marks_boylat_hydro
    ((CAST((validity, last_found, geom,
            datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
            scamin, picrep, txtdsc, sordat, sorind,
            colour, colpat, conrad, marsys, boyshp, catlam, 0
        ) AS waterway.fairway_marks_boylat_hydro)
    ));

CREATE TABLE waterway.fairway_marks_boylat_ienc (
    id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
) INHERITS (waterway.fairway_marks_boylat_new);
CREATE UNIQUE INDEX fairway_marks_boylat_ienc_distinct_rows
    ON waterway.fairway_marks_boylat_ienc
    ((CAST((validity, last_found, geom,
            datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
            scamin, picrep, txtdsc, sordat, sorind,
            colour, colpat, conrad, marsys, boyshp, catlam, 0
        ) AS waterway.fairway_marks_boylat_ienc)
    ));

-- Assume all features are IENC features, since there are currently only
-- very few features in the data source for HYDRO features
INSERT INTO waterway.fairway_marks_boylat_ienc
    OVERRIDING USER VALUE
    SELECT validity, last_found, geom,
            datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
            scamin, picrep, txtdsc, sordat, sorind,
            colour, colpat, conrad, marsys, boyshp, catlam, id
        FROM waterway.fairway_marks_boylat;

-- Finally
DROP TABLE waterway.fairway_marks_boylat;
ALTER TABLE waterway.fairway_marks_boylat_new RENAME TO fairway_marks_boylat;

GRANT SELECT on ALL tables in schema waterway TO waterway_user;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway
    TO waterway_admin;