[postgis-users] Repeat operations on 50 tables of the same schema?

[Hugues François]

Hi, 

Sounds more like a generic postgresql question and you should be able to find most of the answers by reading the documentation. However, here are some basic suggestions: 
- Add a prefix to the name of the table: "IGN_bdTopo_" 
- Add a suffix to the table name: "_V1" 
=> For both I would create a PLPGSQL function in order to loop over the table (list can be retrieved from postgreSQL catalogs or information_schema and execute the required alter table / rename statements. 
- 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'. 
=> These are usual operations: alter table + add column and then update. Read the manual for details 
- move all the elements of these 50 tables (including all). from the "ign" schema to the "ign_v2" schema. Whether data, constraints, indexes. 
=> same thing as above using alter schema / rename, no need to move anything 

Regards, 
Hug 





De: "celati Laurent" <laurent.celati at gmail.com> 
À: "PostGIS Users Discussion" <postgis-users at lists.osgeo.org> 
Envoyé: Lundi 27 Février 2023 11:49:40 
Objet: [postgis-users] Repeat operations on 50 tables of the same schema? 

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. 


[Fichier texte:ATT00001] 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20230227/3b914344/attachment.htm>