postgresql 如何将数据库中的所有列转换为不区分大小写
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10086532/
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
How can I convert all columns in my database to case insensitive
提问by CSharpened
I have seen that it is possible to convert all tablesto case insensitive names using the following commands in psql:
我已经看到可以使用 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 columnsto case insensitive in the same way. How can this be achieved?
我一直无法找到以相同方式将所有列转换为不区分大小写的命令。如何做到这一点？
EDIT: 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?
编辑：显然上面的代码只将表名转换为小写。我知道这段代码 ALTER TABLE "YourTableName" RENAME TO YourTableName; 将转换为表名不区分大小写。有没有办法对列名的质量执行类似的功能？
回答by El Yobo
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.
按照与原始相同的思路，您应该能够执行以下操作。这将重命名所有尚未使用小写的列，方法是从 information_schema 中提取它们，为更改生成 SQL，将其存储到文件中，然后再次执行 SQL。
\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
回答by vyegorov
By default, all you identifiers are case insensitive, and internally PostgreSQL stores them in lowercase. In case you need to have:
默认情况下，所有标识符都不区分大小写，并且 PostgreSQL 在内部以小写形式存储它们。如果您需要：
- case sensitive
- non-ASCII characters
- special characters
- 区分大小写
- 非 ASCII 字符
- 特殊字符
within your identifiers, you should use double quotes (") around your identifiers.
在您的标识符中，您应该"在标识符周围使用双引号 ( )。
Please, check this bitof the PostgreSQL documentation.
请检查该位PostgreSQL的文档。
EDIT:After your clarification, you can use:
编辑：澄清后，您可以使用：
SELECT 'ALTER TABLE '||quote_ident(t.relname)||' RENAME TO '||t.relname||';'
FROM pg_class t, pg_namespace s
WHERE s.oid = t.relnamespace AND s.nspname = 'public'
AND t.relkind='r' AND t.relname != lower(t.relname)
ORDER BY 1;
and for columns:
和列：
SELECT 'ALTER TABLE '||quote_ident(t.relname)||
' RENAME COLUMN '||quote_ident(a.attname)||
' TO '||a.attname||';'
FROM pg_class t, pg_namespace s, pg_attribute a
WHERE s.oid = t.relnamespace AND s.nspname = 'public'
AND t.relkind='r'
AND a.attrelid = t.oid AND NOT a.attisdropped AND a.attnum > 0
AND a.attname != lower(a.attname)
ORDER BY 1;
Then copy-paste the output into your client.
然后将输出复制粘贴到您的客户端。
If you're using psql, you can use \tto enable rows-only mode, \o <full_file_path>to save output into the temporary file and, finally, \i <full_file_path>to execute actual statements.
如果您正在使用psql，则可以使用\t来启用仅行模式，\o <full_file_path>将输出保存到临时文件中，最后\i <full_file_path>执行实际语句。
回答by Evan Carroll
I created a SQL query on Database Administrators that does just this.
我在数据库管理员上创建了一个 SQL 查询来执行此操作。
- Converts all identifiers to lower case
- Converts spaces
' 'to'_' - Does this for all schema, table, and column names
- 将所有标识符转换为小写
- 将空格转换
' '为'_' - 对所有架构、表和列名称执行此操作
For more information see,
有关更多信息，请参阅，
回答by Clodoaldo Neto
do language plpgsql $$
declare
r record;
begin
for r in
select relname, attname
from pg_attribute a
inner join pg_class c on a.attrelid = c.oid
inner join pg_namespace n on c.relnamespace = n.oid
where
n.nspname = 'public'
and
attname != lower(attname)
and
not attisdropped
loop
execute format('
alter table %1$I rename column %2$I to %3$s
', r.relname, r.attname, lower(r.attname));
end loop;
end;
$$;
Issue a begin;before trying this. Check if it is correct. Only then issue a commit;. If you are using a namespace then substitute it in the whereclause.
begin;在尝试之前发出一个。检查它是否正确。只有这样才能发出一个commit;. 如果您使用命名空间，则在where子句中替换它。
