Postgresql : Repeat operations on 50 tables of the same schema?

I am new to Postgresql (v.13). I have 50 tables into a "ign" schema (schema other than public). I would like for each of these 50 tables:

• Add a prefix to the name of the table: "IGN_bdTopo_"

• Add a suffix to the table name: "_V1"

• Create a new "date" column of date type. And populate this field with the value: 06/15/2021

• Create a new "source" column of type varchar (length 50). And populate this field with the value: 'ign'.

• Move all the elements of these 50 tables (including all). from the "ign" schema to the "ign_v2" schema. Whether data, constraints, indexes.

Can someone could help me?

For bulk operations like that, psql's \gexec is invaluable. It allows you to write an SQL statement that generates SQL statements and then execute each result row as a statement. For the rename, that could look like this:

SELECT format(
          'ALTER TABLE ign.%I RENAME TO %i',
          table_name,
          'IGN_bdTopo_' || table_name || 'V1'
       )
FROM information_schema.tables
WHERE table_schema = 'ign'
  AND table_type = 'BASE TABLE' \gexec

The metadata views in information_schema are very useful for this, and the format() function makes it easy to avoid SQL injection problems.

I recommend not to use upper case characters in object names.

Your code structure will be as below. You can add other operations which you need.

DO $$
DECLARE
table_names text;
schema_names text='public';
BEGIN
FOR table_names IN 
select table_name  FROM information_schema.tables
WHERE table_schema = schema_names
LOOP
RAISE NOTICE 'added columns to %', table_names;
EXECUTE 'alter table "'||schema_names||'"."' || table_names || '" add  column if not exists  date date ';
EXECUTE 'alter table "'||schema_names||'"."' || table_names || '" add column if not exists source character varying(50)';
END LOOP;
END;
$$;