Postgresql中的奇怪行为 [英] Strange behaviour in Postgresql
问题描述
我是Postgresql的新手，正在尝试从MySQL迁移应用程序.
我有一个具有以下结构的表:
I'm new to Postgresql and I'm trying to migrate my application from MySQL.
I have a table with the following structure:
Table "public.tbl_point"
Column | Type | Modifiers | Storage | Description
------------------------+-----------------------+-----------+----------+-------------
Tag_Id | integer | not null | plain |
Tag_Name | character varying(30) | not null | extended |
Quality | integer | not null | plain |
Execute | integer | not null | plain |
Output_Index | integer | not null | plain |
Last_Update | abstime | | plain |
Indexes:
"tbl_point_pkey" PRIMARY KEY, btree ("Tag_Id")
Triggers:
add_current_date_to_tbl_point BEFORE UPDATE ON tbl_point FOR EACH ROW EXECUTE PROCEDURE update_tbl_point()
Has OIDs: no
当我使用libpq通过C程序运行查询时:
when I run the query through a C program using libpq:
UPDATE tbl_point SET "Execute"=0 WHERE "Tag_Id"=0
我得到以下输出:
ERROR: record "new" has no field "last_update"
CONTEXT: PL/pgSQL function "update_tbl_point" line 3 at assignment
当我尝试使用pgAdminIII更改"Execute"或任何其他列的值时，我得到完全相同的错误.
I get exactly the same error when I try to change the value of "Execute" or any other column using pgAdminIII.
如果将列名从"Last_Update"更改为"last_update"，一切都可以正常工作.
Everything works fine if I change the column name from "Last_Update" to "last_update".
我在数据库中的其他表中发现了同样的问题，并且该列始终与abstime或timestamp列一起出现.
I found the same problem with other tables I have in my database and the column always appears with abstime or timestamp columns.
推荐答案
您的update_tbl_point函数可能正在执行以下操作:
Your update_tbl_point function is probably doing something like this:
new.last_update = current_timestamp;
，但是它应该使用new."Last_Update"，因此请修复您的触发功能.
but it should be using new."Last_Update" so fix your trigger function.
列名已在PostgreSQL中标准化为小写(与SQL标准所说的相反)，但是用双引号引起来的标识符保持其大小写:
Column names are normalized to lower case in PostgreSQL (the opposite of what the SQL standard says mind you) but identifiers that are double quoted maintain their case:
引用标识符也使其区分大小写，而未加引号的名称总是折叠为小写.例如，PostgreSQL认为标识符FOO，foo和"foo"是相同的，但"Foo"和"FOO"彼此之间是不同的. (在PostgreSQL中，将未加引号的名称折叠成小写是与SQL标准不兼容的，SQL标准说，未加引号的名称应该被折叠成大写.因此，根据标准，foo应该等于"FOO"而不是"foo".您要编写可移植的应用程序时，建议您始终引用一个特定名称或永远不要引用它.)
Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)
因此，如果您这样做:
create table pancakes (
Eggs integer not null
)
然后您可以执行以下任何操作:
then you can do any of these:
update pancakes set eggs = 11;
update pancakes set Eggs = 11;
update pancakes set EGGS = 11;
，它将起作用，因为所有三种形式均被标准化为eggs.但是，如果您这样做:
and it will work because all three forms are normalized to eggs. However, if you do this:
create table pancakes (
"Eggs" integer not null
)
然后您可以执行以下操作:
then you can do this:
update pancakes set "Eggs" = 11;
但不是这样:
update pancakes set eggs = 11;
PostgreSQL的通常做法是在所有地方都使用小写标识符，这样您就不必担心它了.我也建议在其他数据库中使用相同的命名方案，因为必须用引号将SQL加上双引号(标准)，反引号(MySQL)和方括号(SQL Server)，否则不会认识你吧！
The usual practice with PostgreSQL is to use lower case identifiers everywhere so that you don't have to worry about it. I'd recommend the same naming scheme in other databases as well, having to quote everything just leaves you with a mess of double quotes (standard), backticks (MySQL), and brackets (SQL Server) in your SQL and that won't make you any friends.
这篇关于Postgresql中的奇怪行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
