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

[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] Repeat operations on 50 tables of the same schema?
From:       Hugues =?utf-8?Q?Fran=C3=A7ois?= <hugues.francois () inrae ! fr>
Date:       2023-02-27 11:19:00
Message-ID: 319861851.27664046.1677496740839.JavaMail.zimbra () inrae ! fr
[Download RAW message or body]

[Attachment #2 (multipart/alternative)]

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@gmail.com> 
À: "PostGIS Users Discussion" <postgis-users@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] 

[Attachment #5 (text/html)]

<html><body><div id="zimbraEditorContainer" style="font-family: garamond,new \
york,times,serif; font-size: 12pt; color: #000000" class="2"><div>Hi,</div><div><br \
data-mce-bogus="1"></div><div>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:</div><div> <!--StartFragment--><span style="color: \
#000000; font-family: garamond, 'new york', times, serif; font-size: 16px; \
font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; \
font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: \
0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; \
-webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: \
initial; text-decoration-style: initial; text-decoration-color: initial; display: \
inline !important; float: none;" data-mce-style="color: #000000; font-family: \
garamond, 'new york', times, serif; font-size: 16px; font-style: normal; \
font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; \
letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; \
text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; \
-webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: \
initial; text-decoration-style: initial; text-decoration-color: initial; display: \
inline !important; float: none;">- Add a prefix to the name of the table: \
"IGN_bdTopo_"</span></div><div><span style="color: #000000; font-family: garamond, \
'new york', times, serif; font-size: 16px; font-style: normal; \
font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; \
letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; \
text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; \
-webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: \
initial; text-decoration-style: initial; text-decoration-color: initial; display: \
inline !important; float: none;" data-mce-style="color: #000000; font-family: \
garamond, 'new york', times, serif; font-size: 16px; font-style: normal; \
font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; \
letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; \
text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; \
-webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: \
initial; text-decoration-style: initial; text-decoration-color: initial; display: \
inline !important; float: none;">- Add a suffix to the table name: \
"_V1"</span></div><div>=&gt; 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.<br \
style="color: #000000; font-family: garamond, 'new york', times, serif; font-size: \
16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; \
font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: \
0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; \
-webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: \
initial; text-decoration-style: initial; text-decoration-color: initial;" \
data-mce-style="color: #000000; font-family: garamond, 'new york', times, serif; \
font-size: 16px; font-style: normal; font-variant-ligatures: normal; \
font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; \
text-align: start; text-indent: 0px; text-transform: none; white-space: normal; \
widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: \
#ffffff; text-decoration-thickness: initial; text-decoration-style: initial; \
text-decoration-color: initial;"><span style="color: #000000; font-family: garamond, \
'new york', times, serif; font-size: 16px; font-style: normal; \
font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; \
letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; \
text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; \
-webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: \
initial; text-decoration-style: initial; text-decoration-color: initial; display: \
inline !important; float: none;" data-mce-style="color: #000000; font-family: \
garamond, 'new york', times, serif; font-size: 16px; font-style: normal; \
font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; \
letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; \
text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; \
-webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: \
initial; text-decoration-style: initial; text-decoration-color: initial; display: \
inline !important; float: none;">- create a new "date" column of date type. And \
populate this field with the value: 06/15/2021</span><br style="color: #000000; \
font-family: garamond, 'new york', times, serif; font-size: 16px; font-style: normal; \
font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; \
letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; \
text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; \
-webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: \
initial; text-decoration-style: initial; text-decoration-color: initial;" \
data-mce-style="color: #000000; font-family: garamond, 'new york', times, serif; \
font-size: 16px; font-style: normal; font-variant-ligatures: normal; \
font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; \
text-align: start; text-indent: 0px; text-transform: none; white-space: normal; \
widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: \
#ffffff; text-decoration-thickness: initial; text-decoration-style: initial; \
text-decoration-color: initial;"><span style="color: #000000; font-family: garamond, \
'new york', times, serif; font-size: 16px; font-style: normal; \
font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; \
letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; \
text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; \
-webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: \
initial; text-decoration-style: initial; text-decoration-color: initial; display: \
inline !important; float: none;" data-mce-style="color: #000000; font-family: \
garamond, 'new york', times, serif; font-size: 16px; font-style: normal; \
font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; \
letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; \
text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; \
-webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: \
initial; text-decoration-style: initial; text-decoration-color: initial; display: \
inline !important; float: none;">- create a new "source" column of type varchar \
(length 50). And populate this field with the value: 'ign'.</span></div><div>=&gt; \
These are usual operations: alter table + add column and then update. Read the manual \
for details<br style="color: #000000; font-family: garamond, 'new york', times, \
serif; font-size: 16px; font-style: normal; font-variant-ligatures: normal; \
font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; \
text-align: start; text-indent: 0px; text-transform: none; white-space: normal; \
widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: \
#ffffff; text-decoration-thickness: initial; text-decoration-style: initial; \
text-decoration-color: initial;" data-mce-style="color: #000000; font-family: \
garamond, 'new york', times, serif; font-size: 16px; font-style: normal; \
font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; \
letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; \
text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; \
-webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: \
initial; text-decoration-style: initial; text-decoration-color: initial;"><span \
style="color: #000000; font-family: garamond, 'new york', times, serif; font-size: \
16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; \
font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: \
0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; \
-webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: \
initial; text-decoration-style: initial; text-decoration-color: initial; display: \
inline !important; float: none;" data-mce-style="color: #000000; font-family: \
garamond, 'new york', times, serif; font-size: 16px; font-style: normal; \
font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; \
letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; \
text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; \
-webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: \
initial; text-decoration-style: initial; text-decoration-color: initial; display: \
inline !important; float: none;">- move all the elements of these 50 tables \
(including all). from the "ign" schema to the "ign_v2" schema. Whether data, \
constraints, indexes.</span><!--EndFragment--></div><div><span style="color: #000000; \
font-family: garamond, 'new york', times, serif; font-size: 16px; font-style: normal; \
font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; \
letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; \
text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; \
-webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: \
initial; text-decoration-style: initial; text-decoration-color: initial; display: \
inline !important; float: none;" data-mce-style="color: #000000; font-family: \
garamond, 'new york', times, serif; font-size: 16px; font-style: normal; \
font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; \
letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; \
text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; \
-webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: \
initial; text-decoration-style: initial; text-decoration-color: initial; display: \
inline !important; float: none;">=&gt; same thing as above using alter schema / \
rename, no need to move anything</span></div><div><span style="color: #000000; \
font-family: garamond, 'new york', times, serif; font-size: 16px; font-style: normal; \
font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; \
letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; \
text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; \
-webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: \
initial; text-decoration-style: initial; text-decoration-color: initial; display: \
inline !important; float: none;" data-mce-style="color: #000000; font-family: \
garamond, 'new york', times, serif; font-size: 16px; font-style: normal; \
font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; \
letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; \
text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; \
-webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: \
initial; text-decoration-style: initial; text-decoration-color: initial; display: \
inline !important; float: none;"><br data-mce-bogus="1"></span></div><div><span \
style="color: #000000; font-family: garamond, 'new york', times, serif; font-size: \
16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; \
font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: \
0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; \
-webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: \
initial; text-decoration-style: initial; text-decoration-color: initial; display: \
inline !important; float: none;" data-mce-style="color: #000000; font-family: \
garamond, 'new york', times, serif; font-size: 16px; font-style: normal; \
font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; \
letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; \
text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; \
-webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: \
initial; text-decoration-style: initial; text-decoration-color: initial; display: \
inline !important; float: none;">Regards,</span></div><div><span style="color: \
#000000; font-family: garamond, 'new york', times, serif; font-size: 16px; \
font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; \
font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: \
0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; \
-webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: \
initial; text-decoration-style: initial; text-decoration-color: initial; display: \
inline !important; float: none;" data-mce-style="color: #000000; font-family: \
garamond, 'new york', times, serif; font-size: 16px; font-style: normal; \
font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; \
letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; \
text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; \
-webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-thickness: \
initial; text-decoration-style: initial; text-decoration-color: initial; display: \
inline !important; float: none;">Hug</span></div><div \
data-marker="__SIG_PRE__"><div><p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; \
font-size: 11pt; font-family: Calibri, sans-serif; background-color: #fdfcfa;"><br \
data-mce-bogus="1"></p></div></div><div><br></div><hr id="zwchr" \
data-marker="__DIVIDER__"><div data-marker="__HEADERS__"><b>De: </b>"celati Laurent" \
&lt;laurent.celati@gmail.com&gt;<br><b>À: </b>"PostGIS Users Discussion" \
&lt;postgis-users@lists.osgeo.org&gt;<br><b>Envoyé: </b>Lundi 27 Février 2023 \
11:49:40<br><b>Objet: </b>[postgis-users] Repeat operations on 50 tables of the same \
schema?<br></div><div><br></div><div data-marker="__QUOTED_TEXT__"><div \
dir="ltr">Good morning,<br>I am new to Postgresql. I have 50 tables into a "ign" \
schema (schema other than public).<br>I would like for each of these 50 \
tables:<br><br>- Add a prefix to the name of the table: "IGN_bdTopo_"<br>- Add a \
suffix to the table name: "_V1"<br>- create a new "date" column of date type. And \
populate this field with the value: 06/15/2021<br>- create a new "source" column of \
type varchar (length 50). And populate this field with the value: 'ign'.<br>- move \
all the elements of these 50 tables (including all). from the "ign" schema to the \
"ign_v2" schema. Whether data, constraints, indexes.<br><br>If someone could&nbsp; \
help me? Thank you so much.<br></div> <br><br>[Fichier \
texte:ATT00001]<br></div></div></body></html>

_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[prev in list] [next in list] [prev in thread] [next in thread] 