重命名PostgreSQL中多列 [英] Renaming multiple columns in PostgreSQL
问题描述
我的表在以下格式一堆列:
My table has a bunch of columns in the following format:
_settingA
_settingB
_settingB
和我想他们只需重命名添加preFIX如下:
And I want to rename them simply to add a prefix as follows:
_1_settingA
_1_settingB
_1_settingC
什么是实现这一目标的最快/最有效的方式?
What is the quickest / most efficient way to achieve this?
编辑:我要补充一点,我有一个的很多的比三列更多以这种方式命名。如果我有短短三年,我只是做手工一个接一个。并感谢downvote不管你是谁。
I should add that I have a lot more than three columns to rename in this way. If I had just three, I'd just do it manually one by one. And thanks for the downvote whoever you are.
推荐答案
有没有一个命令的形式给出。很显然,你可以通过你的自我为重命名
键入多个命令对应的,但让我intoduce一些改进:)我在的这个答案
There's no single command aproach. Obviously you could type multiple comands for RENAME
by your self, but let me intoduce some improvement:) As I said in this answer
......所有这类散装管理员的操作,您可以使用PostgreSQL的系统表生成的查询为你而不是用手写其中
...for all such bulk-admin-operations you could use PostgreSQL system tables to generate queries for you instead of writing them by hand
在你的情况将是:
SELECT
'ALTER TABLE ' || tab_name || ' RENAME COLUMN '
|| quote_ident(column_name) || ' TO '
|| quote_ident( '_1' || column_name) || ';'
FROM (
SELECT
quote_ident(table_schema) || '.' || quote_ident(table_name) as tab_name,
column_name
FROM information_schema.columns
WHERE
table_schema = 'schema_name'
AND table_name = 'table_name'
AND column_name LIKE '\_%'
) sub;
这会给你设置字符串这是像SQL命令:
That'll give you set of strings which are SQL commands like:
ALTER TABLE schema_name.table_name RENAME COLUMN "_settingA" TO "_1_settingA";
ALTER TABLE schema_name.table_name RENAME COLUMN "_settingB" TO "_1_settingB";
...
有使用 TABLE_SCHEMA
在 WHERE
子句没有必要,如果你的表是公共
模式。 还记得使用功能 quote_ident()
- 看我原来的答复更多的解释
There no need using table_schema
in WHERE
clause if your table is in public
schema. Also remember using function quote_ident()
-- read my original answer for more explanation.
编辑:
我已经所以现在它的工作原理与名称以下划线 _
开始时所有的列改变我的查询。因为下划线是SQL模式匹配特殊字符,我们必须逃避它(使用 \\
)以acctually找到它。
I've change my query so now it works for all columns with name begining with underscore _
. Because underscore is special character in SQL pattern matching, we must escape it (using \
) to acctually find it.
这篇关于重命名PostgreSQL中多列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!