postgresql 在 postgres 中将表列名称更改为大写
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14351743/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Change table column names to upper case in postgres
提问by jobi88
I am using postgres 9.2. I need to change all column name to UPPER CASE for all tables in postgres db.
我正在使用 postgres 9.2。我需要将 postgres db 中所有表的所有列名更改为大写。
Is there any way to do this?? Do i need to change any configurations in postgres?
有没有办法做到这一点？我需要更改 postgres 中的任何配置吗？
回答by mvp
Before I explain how to do this, I would stronglysuggest NOT doing that.
在我解释如何做到这一点之前，我强烈建议不要这样做。
In PostgreSQL, if table or column names are unquoted, like:
在 PostgreSQL 中，如果表名或列名未加引号，例如：
SELECT Name FROM MyTable WHERE ID = 10
They actually automatically folded to lower casefirst, so query above is identical to:
它们实际上首先自动折叠为小写，因此上面的查询与：
SELECT name FROM mytable WHERE id = 10
If you were to convert all names to upper case, this statement will NOT work:
如果您要将所有名称转换为大写，则此语句将不起作用：
SELECT NAME FROM MYTABLE WHERE ID = 10
You will have to double-quote every single name in this query to make it work:
您必须在此查询中用双引号引用每个名称才能使其工作：
SELECT "NAME" FROM "MYTABLE" WHERE "ID" = 10
If, on other hand, you use standard PostgreSQL lower-case only agreement, you can use any case combination and it will work as long as you do not quote any name.
另一方面，如果您使用标准的 PostgreSQL 仅小写协议，则可以使用任何大小写组合，只要您不引用任何名称，它就会起作用。
Now, if you still insist to convert to upper case, you can do that by dumping your database schema into a file using pg_dump --schema-only.
现在，如果您仍然坚持转换为大写，您可以通过使用pg_dump --schema-only.
After you've done that, check all CREATE TABLEstatements and construct appropriate ALTER TABLEstatements based on this dump - you will have to write some script (Perl or Python) to do that.
完成后，检查所有CREATE TABLE语句并ALTER TABLE基于此转储构造适当的语句 - 您必须编写一些脚本（Perl 或 Python）来执行此操作。
Alternatively, you can read INFORMATION_SCHEMA.TABLESand/or INFORMATION_SCHEMA.COLUMNSand also construct and execute appropriate ALTER TABLEstatements.
或者，您可以阅读INFORMATION_SCHEMA.TABLES和/或INFORMATION_SCHEMA.COLUMNS并构造和执行适当的ALTER TABLE语句。
回答by Ashish Bajpai
Below query create SQL statements that you can run to change column names of a table to lowercase. Remove table_name check if you want to apply this broadly. For more details refer this post
下面的查询创建 SQL 语句，您可以运行这些语句将表的列名更改为小写。如果您想广泛应用它，请删除 table_name 检查。有关更多详细信息，请参阅此帖子
SELECT array_to_string(ARRAY(SELECT 'ALTER TABLE ' || quote_ident(c.table_schema) || '.'
|| quote_ident(c.table_name) || ' RENAME "' || c.column_name || '" TO ' || quote_ident(lower(c.column_name)) || ';'
FROM information_schema.columns As c
WHERE c.table_schema NOT IN('information_schema', 'pg_catalog')
AND c.column_name <> lower(c.column_name)
and table_name = 'your_table_name'
ORDER BY c.table_schema, c.table_name, c.column_name
) ,
E'\r') As ddlsql;
