如何将数据库中的所有列转换为不区分大小写 [英] How can I convert all columns in my database to case insensitive

问题描述

我已经看到可以使用psql中的以下命令将所有表转换为不区分大小写的名称：

I have seen that it is possible to convert all tables to case insensitive names using the following commands in psql:

\o /tmp/go_to_lower
select 'ALTER TABLE '||'"'||tablename||'"'||' RENAME TO ' ||
lower(tablename)||';' from pg_tables where schemaname = 'public';
psql -U username database < /tmp/go_to_lower

我无法挖掘命令来转换所有列以不区分大小写的方式进行。

I have been unable to unearth a command to convert all columns to case insensitive in the same way. How can this be achieved?

编辑：显然以上代码仅将表名转换为小写。我知道这段代码ALTER TABLE YourTableName重命名为YourTableName;将转换为不区分大小写的表名。

Apparently the above code only converts table names to lower case. I am aware that this code ALTER TABLE "YourTableName" RENAME TO YourTableName; will convert to case insensitive for a table name. Is there a way to do a similar function on mass for column names?

推荐答案

与原始行相同，然后，您应该能够执行以下操作。通过从information_schema中提取它们，为更改生成SQL，将其存储到文件中，然后再次执行SQL，来重命名所有尚未小写的列。

Along the same lines as the original, then, you should be able to do the following. This renames all columns that are not already in lower case, by extracting them from the information_schema, generating SQL for the changes, storing it to a file then executing the SQL again.

\t on
select 'ALTER TABLE '||'"'||table_name||'"'||' RENAME COLUMN '||'"'||column_name||'"'||' TO ' || lower(column_name)||';' 
from information_schema.columns 
where table_schema = 'public' and lower(column_name) != column_name
\g /tmp/go_to_lower
\i /tmp/go_to_lower

这篇关于如何将数据库中的所有列转换为不区分大小写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！