secret
Last active

  • Download Gist
fixing_constraints.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
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$$;

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.