Alchemy

update rogan.ebrary_beta_isbn set value = regexp_replace(value,' (.*)','') where value like '% %';
-- clean up ISBNs

select KCU.TABLE_SCHEMA, KCU.TABLE_NAME
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
    on CCU.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
    and CCU.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA and CCU.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON KCU.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
    AND KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE CCU.TABLE_SCHEMA = 'permission' AND CCU.TABLE_NAME = 'grp_tree' AND  CCU.COLUMN_NAME = 'id'
;


CREATE TABLE rogan.test_items AS
        SELECT id FROM
        (
                SELECT xyz.id, ROW_NUMBER() OVER (PARTITION BY aou.parent_ou) AS row_num
                FROM (
                        SELECT DISTINCT ON (ac.circ_modifier, ac.circ_lib) ac.id
                        FROM asset.COPY ac
                        JOIN asset.copy_location acl ON acl.id = ac.LOCATION
                        WHERE ac.deleted = FALSE AND ac.status = 0 AND ac.circulate = TRUE AND acl.circulate = TRUE AND ac.circ_modifier != 'BOOK'
                ) xyz
                JOIN asset.COPY ac ON ac.id = xyz.id
                JOIN actor.org_unit aou ON aou.id = ac.circ_lib
                WHERE aou.ou_type = 3
        ) zyx WHERE row_num < 5
;