Duplicate all tables in Postgres

Duplicate all tables in Postgres.md

duplicate all tables under 'public'

do
$$
declare
    r record;
    prefix text := 'copy_1_';
    total integer := (SELECT count(table_name)::integer FROM information_schema.tables WHERE table_schema = 'public');
    current integer := 0;
begin
    for r in
        SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'
    loop
        current := current + 1;
        raise notice '[%/%] working on %_% ...', current, total, prefix, r.table_name;
        execute format('
            create table if not exists %1$s_%2$s as
                select * from %2$s
        ', prefix, r.table_name);
        execute format('
            create index if not exists idx_%1$s_%2$s on %1$s_%2$s using btree (date)
        ', prefix, r.table_name);
    end loop;
end;
$$;

create index on 'date' column for each table

do
$$
declare
    r record;
begin
    for r in
        SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'
    loop
        execute format('
            create index if not exists idx_%s on %s using btree (date)
        ', r.table_name, r.table_name);
    end loop;
end;
$$;