Re: Repear operations on 50 tables of the same schema? - Mailing list pgsql-general

On 2/27/23 05:53, celati Laurent wrote:

Good morning,
I am new to Postgresql. 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.

If someone could  help me? Thank you so much.

This is what I'd do, just to get it done.  It presumes you know bash scripting, and how to use psql.

https://www.postgresql.org/docs/13/sql-createschema.html
https://www.postgresql.org/docs/13/sql-altertable.html

First, CREATE SCHEMA ign_v2;

Write a bash script that uses psql queries information_schema.tables.  There's be a for loop for all the tables.

Inside the loop, still using bash, and using psql:
1. create the new table name then execute "ALTER TABLE ... RENAME TO ...;",
2. ALTER TABLE (new_name) ADD COLUMN some_date DATE, ADD COLUMN some_source varchar(50);
3. UPDATE (new_name) SET some_date = '2021-06-15'::date, some_source = 'ign';
4. ALTER TABLE (new_name) SET SCHEMA ign_v2;