To drop tables, you can generate a list of tables from pg_tables.
For example, if I generate tables from generate reports and I want to
delete by the end date on the table name, I can do the following:
1
2
3
4
5
6
7
| SELECT 'DROP TABLE IF EXISTS "' || schemaname || '"."' || tablename || '" CASCADE;'
FROM pg_tables
WHERE schemaname = 'generate_reports'
AND (CASE WHEN RIGHT(tablename,8) !~ '^(\+|-)?[[:digit:]]+$'
THEN NULL::DATE
ELSE RIGHT(tablename,8)::DATE
END) < '2022-06-14';
|
This will take for an example
generate_reports.8l90_stats_dailsummary_20220601_20220608 would get deleted
but if 20220608 was instead of 20220620 then it would
not get deleted.
1
2
3
4
5
6
7
| DROP TABLE IF EXISTS "generate_reports"."8l90_statstsallallunitdata_20220606_20220613" CASCADE;
DROP TABLE IF EXISTS "generate_reports"."8l90_statstsallsummary_20220606_20220613" CASCADE;
DROP TABLE IF EXISTS "generate_reports"."8l90_statstsalldailysummary_20220606_20220613" CASCADE;
DROP TABLE IF EXISTS "generate_reports"."8l90_statstsallmodelstested_20220606_20220613" CASCADE;
DROP TABLE IF EXISTS "generate_reports"."8l90_statstsallmodelsstats_20220606_20220613" CASCADE;
DROP TABLE IF EXISTS "generate_reports"."8l90_statstsallopfail_20220606_20220613" CASCADE;
DROP TABLE IF EXISTS "generate_reports"."8l90_statstsalltpfail_20220606_20220613" CASCADE;
|
Then I can copy and paste to delete these tables.