Postgres区分大小写 [英] Postgres Case Sensitivity

问题描述

我已通过工具从MSSql Server 2008从Postgres中导入了100个表，该工具在大写字母中创建了所有表及其列.现在，如果我想从表中获取数据视图，例如-STD_TYPE_CODES as-

I have imported 100 of tables in Postgres from MSSql server 2008 through tool which created all the tables along with their columns in capital letter. Now if I want to make a data view from table e.g - STD_TYPE_CODES as-

select * from STD_TYPE_CODES

我遇到以下错误-

ERROR:  relation "std_type_codes" does not exist
LINE 1: select * from STD_TYPE_CODES
                  ^
********** Error **********
ERROR: relation "std_type_codes" does not exist
SQL state: 42P01
Character: 15

我知道我可以在表名两边加上引号-

I know I can put the quotes around the table name as-

select * from "STD_TYPE_CODES"

但是当我使用MSSql Server时，没有这种问题. 那么有什么办法可以摆脱这种情况呢?请帮忙.

But as I have worked with MSSql Server, there is no such kind of issue. So is there any way to get rid of this? Please help.

推荐答案

在PostgreSQL中，未引用的名称不区分大小写.因此SELECT * FROM hello和SELECT * FROM HELLO是等效的.

In PostgreSQL unquoted names are case-insensitive. Thus SELECT * FROM hello and SELECT * FROM HELLO are equivalent.

但是，带引号的名称区分大小写. SELECT * FROM "hello" 不等同于SELECT * FROM "HELLO".

However, quoted names are case-sensitive. SELECT * FROM "hello" is not equivalent to SELECT * FROM "HELLO".

要在加引号的名称和未加引号的名称之间架起一座桥梁"，未加引号的名称会隐式小写，因此hello，HELLO和HeLLo等效于"hello"，但不等同于"HELLO"或(糟糕！).

To make a "bridge" between quoted names and unquoted names, unquoted names are implicitly lowercased, thus hello, HELLO and HeLLo are equivalent to "hello", but not to "HELLO" or "HeLLo" (OOPS!).

因此，在PostgreSQL中创建实体(表，视图，过程等)时，应将其指定为不加引号或加引号但小写.

Thus, when creating entities (tables, views, procedures, etc) in PostgreSQL, you should specify them either unquoted, or quoted-but-lowercased.

要转换现有的表/视图/等，可以使用ALTER TABLE "FOO" RENAME TO "foo"之类的东西.

To convert existing tables/views/etc you can use something like ALTER TABLE "FOO" RENAME TO "foo".

或者，尝试修改MSSQL中的转储，使其与PostgreSQL兼容(以便它包含foo或"foo" s，但不包含"FOO" s).

Or, try to modify dump from MSSQL to make it "PostgreSQL-compatible" (so that it will contain foos or "foo"s but not "FOO"s).

• 通过显式编辑转储文件. (如果您使用的是Linux，则可以执行sed -r 's/"[^"]+"/\L\0/g' dumpfile －但是请注意，此命令还可能会修改字符串文字中的文本.)
• 或者通过从MSSQL获取转储时指定一些选项. (我不确定MSSQL中是否有这样的选项，从未使用过，但可能应该存在这样的选项.)

• Either by explicitly editing dump file. (If you're using Linux, you can do sed -r 's/"[^"]+"/\L\0/g' dumpfile — however be warned that this command may also modify text in string literals.)
• Or by specifying some options when getting dump from MSSQL. (I'm not sure if there are such options in MSSQL, never used it, but probably such options should exist.)

这篇关于Postgres区分大小写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！