Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pWeXW-0002pW-MN for pgsql-general@arkaria.postgresql.org; Mon, 27 Feb 2023 14:31:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pWeXV-0005bZ-Gh for pgsql-general@arkaria.postgresql.org; Mon, 27 Feb 2023 14:31:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pWeXV-0005bP-4P for pgsql-general@lists.postgresql.org; Mon, 27 Feb 2023 14:31:41 +0000 Received: from mail-oa1-x31.google.com ([2001:4860:4864:20::31]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pWeXQ-0001ZW-44 for pgsql-general@lists.postgresql.org; Mon, 27 Feb 2023 14:31:40 +0000 Received: by mail-oa1-x31.google.com with SMTP id 586e51a60fabf-172afa7bee2so7552190fac.6 for ; Mon, 27 Feb 2023 06:31:35 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:content-language:references:to:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=pxmflRTueYEagqOUBBvoJrNAvfXZgY9c8AQnBsO9enc=; b=kjciGwzIb/Fk0Oknu+U5xoRQIqVgyl1jTNvS6M4+autVt6A8ZUEn3w100CoUvx/8lz HCJ35LRcwGEoVpuchVTtp6E+KN1U4tS1OIQ7deKaidk1UTrjH9Fkd7aJAMxsajD4NcBD hxCyJEY+gfbcE7wh8TZ+1erFOAiuNQ68e+890uPJrlRST0hrOrwOx+90p/vMEkN5qzpZ JnjqCooaWqw6HelQ8XvJdYJG05AT1RW/zLnrmIE0q/MOEhChlTsqi8mlGdkm52qO0XDw V0AnSRfbfqa71ZD8H5b6KhCaAuuCKgwgGpEEU5s4KAx/FBCpL3VIfBFSBwSYvtHgmgpi R1dA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:content-language:references:to:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=pxmflRTueYEagqOUBBvoJrNAvfXZgY9c8AQnBsO9enc=; b=2lDXlh3Lx9PifJKLCoByGqyR3215xhwkN0gZjJwOetzWe2A//OSlwY48/jcEvI8PxZ 5dpFvW9gEIHMMhsHDKknbc+M+u+pk7T8J2UxPq5bdcQ40mD/XdBCdEKmtdRIJy7OsW0U nbcRKPgg/rv1I6oE2PgK/q2ecgoo2Yayaq5CkArPLSgRBL1XLNCT3npkq4zo6s9l58nc CnrFrcZvXQ6QqmyGCw9q+WQSGnt1GgaAzYC9Iw7NzXXKAAT1b30ORhiu0ro5t3vDFu1F L5ErJUXme5uSxEBp6p/oYwqUPop8sEUTEyNQJGbj/+MBzsXC+rq8xGpP8YdLYpUdg120 68pQ== X-Gm-Message-State: AO0yUKWFruBb3VKMo6Y4h+UVT3hZei+57jQOXFNmNX9WKPRDA1at4G0h kz0TF6AxVr9rawmMHT90Efu0i6nYtAg= X-Google-Smtp-Source: AK7set/btEaSnKBJ12cxQSjLkLM0Korimr1r2sfHJ8qdoLtxu/SrlZ2Z9AWKJRzu/6IAvgx6j7pErw== X-Received: by 2002:a05:6871:b2a:b0:16e:29f3:df8f with SMTP id fq42-20020a0568710b2a00b0016e29f3df8fmr19420392oab.29.1677508293761; Mon, 27 Feb 2023 06:31:33 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id 186-20020a370ac3000000b0073b878e3f30sm4979563qkk.59.2023.02.27.06.31.33 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 27 Feb 2023 06:31:33 -0800 (PST) Content-Type: multipart/alternative; boundary="------------m5nPQbAHC2w0VKQbe4pYu3lB" Message-ID: Date: Mon, 27 Feb 2023 08:31:32 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: Repear operations on 50 tables of the same schema? To: pgsql-general@lists.postgresql.org References: Content-Language: en-US From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------m5nPQbAHC2w0VKQbe4pYu3lB Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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. --------------m5nPQbAHC2w0VKQbe4pYu3lB Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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.
--------------m5nPQbAHC2w0VKQbe4pYu3lB--