英文:
Postgresql : Repeat operations on 50 tables of the same schema?
问题
我是PostgreSQL (版本13)的新手。我有50个表位于一个名为"ign"的模式（不是public模式）。我想为这50个表的每个表执行以下操作：
-
为表名添加前缀："IGN_bdTopo_"
-
为表名添加后缀："_V1"
-
创建一个新的"date"列，数据类型为日期。并将此字段的值填充为"06/15/2021"
-
创建一个新的"source"列，数据类型为varchar（长度50）。并将此字段的值填充为'ign'。
-
将这50个表的所有元素（包括所有数据、约束和索引）从"ign"模式移动到"ign_v2"模式。
是否有人可以帮助我？
英文:
I am new to Postgresql (v.13). 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.
Can someone could help me?
答案1
得分: 2
对于像这样的批量操作，psql的\gexec功能非常有价值。它允许你编写一个生成SQL语句的SQL语句，然后执行每个结果行作为一个语句。对于重命名操作，可以像这样写：
SELECT format(
'ALTER TABLE ign.%I RENAME TO %i',
table_name,
'IGN_bdTopo_' || table_name || 'V1'
)
FROM information_schema.tables
WHERE table_schema = 'ign'
AND table_type = 'BASE TABLE' \gexec
information_schema中的元数据视图对此非常有用，而format()函数使我们能够轻松避免SQL注入问题。
我建议不要在对象名称中使用大写字符。
英文:
For bulk operations like that, psql's \gexec is invaluable. It allows you to write an SQL statement that generates SQL statements and then execute each result row as a statement. For the rename, that could look like this:
SELECT format(
'ALTER TABLE ign.%I RENAME TO %i',
table_name,
'IGN_bdTopo_' || table_name || 'V1'
)
FROM information_schema.tables
WHERE table_schema = 'ign'
AND table_type = 'BASE TABLE' \gexec
The metadata views in information_schema are very useful for this, and the format() function makes it easy to avoid SQL injection problems.
I recommend not to use upper case characters in object names.
答案2
得分: 1
你的代码结构将如下所示。你可以添加其他你需要的操作。
DO $$
DECLARE
table_names text;
schema_names text='public';
BEGIN
FOR table_names IN
select table_name FROM information_schema.tables
WHERE table_schema = schema_names
LOOP
RAISE NOTICE '为 % 添加列', table_names;
EXECUTE 'ALTER TABLE "'||schema_names||'"."' || table_names || '" ADD COLUMN IF NOT EXISTS date DATE';
EXECUTE 'ALTER TABLE "'||schema_names||'"."' || table_names || '" ADD COLUMN IF NOT EXISTS source CHARACTER VARYING(50)';
END LOOP;
END;
$$;
英文:
Your code structure will be as below. You can add other operations which you need.
DO $$
DECLARE
table_names text;
schema_names text='public';
BEGIN
FOR table_names IN
select table_name FROM information_schema.tables
WHERE table_schema = schema_names
LOOP
RAISE NOTICE 'added columns to %', table_names;
EXECUTE 'alter table "'||schema_names||'"."' || table_names || '" add column if not exists date date ';
EXECUTE 'alter table "'||schema_names||'"."' || table_names || '" add column if not exists source character varying(50)';
END LOOP;
END;
$$;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论