Skip to content

@selenamarie /fixing_constraints.sql secret
Created

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
DO $$
DECLARE myrecord record;
DECLARE theweek text;
BEGIN
FOR myrecord IN SELECT relname, conname from pg_constraint
JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
WHERE consrc ~ 'without' and split_part(relname, '_201', 1)
IN (select table_name from report_partition_info
WHERE partition_column = 'date_processed') LIMIT 1
LOOP
EXECUTE 'ALTER TABLE ' || quote_ident(myrecord.relname)
|| ' DROP CONSTRAINT IF EXISTS '
|| quote_ident(myrecord.conname) || ';';
theweek = substring(myrecord.relname from '........$');
EXECUTE 'ALTER TABLE ' || quote_ident(myrecord.relname)
|| ' ADD CONSTRAINT ' || quote_ident(myrecord.conname)
|| ' CHECK ((date_processed >= timestamptz('
|| quote_literal(to_char(date(theweek), 'YYYY-MM-DD')) || '))'
|| ' AND (date_processed < timestamptz('
|| quote_literal(to_char(date(theweek) + 7, 'YYYY-MM-DD'))
|| ')));';
RAISE NOTICE 'DONE: %', myrecord.relname;
END LOOP;
END$$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.