Postgresql 中的奇怪行为

本文介绍了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 更改执行"或任何其他列的值时，我得到完全相同的错误.

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:

引用标识符也使其区分大小写，而未引用的名称始终折叠为小写.例如，标识符 FOO、foo 和 "foo" 被 PostgreSQL 认为是相同的，但是 "Foo" 和 "FOO" 与这三个不同，并且彼此不同.(PostgreSQL 中将未加引号的名称折叠为小写与 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 )

那么你可以这样做:

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.