在 postgres 中将表列名称更改为大写 [英] Change table column names to upper case in postgres

问题描述

我使用的是 postgres 9.2.我需要将 postgres db 中所有表的所有列名更改为大写.

有没有办法做到这一点?我需要更改 postgres 中的任何配置吗?

解决方案

在我解释如何做到这一点之前，我强烈建议不要这样做.

在 PostgreSQL 中，如果表名或列​​名不带引号，例如:

SELECT Name FROM MyTable WHERE ID = 10

它们实际上首先自动折叠成小写，所以上面的查询是相同的:

SELECT name FROM mytable WHERE id = 10

如果您要将所有名称都转换为大写，则此语句将不起作用:

SELECT NAME FROM MYTABLE WHERE ID = 10

您必须用双引号引用此查询中的每个名称才能使其工作:

SELECT "NAME" FROM "MYTABLE" WHERE "ID" = 10

另一方面，如果您使用标准的 PostgreSQL 仅小写协议，则可以使用任何大小写组合，只要不引用任何名称即可.

<小时>

现在，如果您仍然坚持要转换为大写，您可以通过使用 pg_dump --schema-only.

完成后，检查所有CREATE TABLE 语句并构建适当的 ALTER TABLE 基于语句在此转储上 - 您必须编写一些脚本(Perl 或 Python)来执行此操作.

或者，您可以阅读INFORMATION_SCHEMA.TABLES 和/或 INFORMATION_SCHEMA.COLUMNS 并构建和执行适当的 ALTER TABLE 语句.

I am using postgres 9.2. I need to change all column name to UPPER CASE for all tables in postgres db.

Is there any way to do this?? Do i need to change any configurations in postgres?

解决方案

Before I explain how to do this, I would strongly suggest NOT doing that.

In PostgreSQL, if table or column names are unquoted, like:

SELECT Name FROM MyTable WHERE ID = 10

They actually automatically folded to lower case first, 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.

---

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.

After you've done that, check all CREATE TABLE statements and construct appropriate ALTER TABLE statements based on this dump - you will have to write some script (Perl or Python) to do that.

Alternatively, you can read INFORMATION_SCHEMA.TABLES and/or INFORMATION_SCHEMA.COLUMNS and also construct and execute appropriate ALTER TABLE statements.

这篇关于在 postgres 中将表列名称更改为大写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！