Re: Repear operations on 50 tables of the same schema?

[Ron <ronljohnsonjr@xxxxxxxxx>]

    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;

--
Born in Arizona, moved to Babylonia.