Strange behaviour in Postgresql

Question

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

when I run the query through a C program using libpq:

UPDATE tbl_point SET "Execute"=0 WHERE "Tag_Id"=0

I got the following output:

ERROR:  record "new" has no field "last_update"
CONTEXT:  PL/pgSQL function "update_tbl_point" line 3 at assignment

I get exactly the same error when I try to change the value of "Execute" or any other column using pgAdminIII.

Everything works fine if I change the column name from "Last_Update" to "last_update".

I found the same problem with other tables I have in my database and the column always appears with abstime or timestamp columns.

Beffrey

Answer 1

Your update_tbl_point function is probably doing something like this:

new.last_update = current_timestamp;

but it should be using new."Last_Update" so fix your trigger function.

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:

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.)

So, if you do this:

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;

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;

but not this:

update pancakes set eggs = 11;

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.

Democrat

Thank you my friend! Your explanation was flawless! Alexandra. – Beffrey