postgressql constraints fix

postgresql-constraints.sql

-- select all duplicate layouts
with agg as (SELECT name, type, owner FROM layouts GROUP BY name, type, owner HAVING COUNT(*) > 1)

SELECT
    l.*
FROM agg
JOIN layouts l ON l.name = agg.name AND l.type = agg.type AND l.owner = agg.owner;

-- select all duplicate prefs
with agg as (SELECT app, "user" FROM prefs GROUP BY app, "user" HAVING COUNT(*) > 1)

SELECT
    p.*
FROM agg
         JOIN prefs p ON p.app = agg.app AND p.user = agg."user";

-- add layouts constraint
ALTER TABLE layouts
    ADD CONSTRAINT layouts__name__type__owner__unique
        UNIQUE ("name", "type", "owner");


-- add prefs constraint
ALTER TABLE prefs
    ADD CONSTRAINT prefs__app__user__unique
        UNIQUE ("app", "user");