问题描述
我已通过工具从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 foo
s 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区分大小写的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!