Strange behaviour in Postgresql

by

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.

More Related Contents:

  1. SQL query column does not exist error
  2. When do Postgres column or table names need quotes and when don’t they?
  3. INSERT with dynamic table name in trigger function
  4. psql: FATAL: Ident authentication failed for user “postgres”
  5. sql statement error: “column .. does not exist”
  6. Use multiple conflict_target in ON CONFLICT clause
  7. PostgreSQL -must appear in the GROUP BY clause or be used in an aggregate function
  8. How to find table creation time?
  9. What is the format for the PostgreSQL connection string / URL?
  10. Go and IN clause in Postgres
  11. How to add “on delete cascade” constraints?
  12. Is there a way to disable function overloading in Postgres
  13. Can I make a plpgsql function return an integer without using a variable?
  14. How can I start PostgreSQL on Windows?
  15. PostgreSQL – max number of parameters in “IN” clause?
  16. SQLAlchemy, Psycopg2 and Postgresql COPY
  17. How to find out if an upsert was an update with PostgreSQL 9.5+ UPSERT?
  18. How to reset sequence in postgres and fill id column with new data?
  19. Differentiate inserted and updated rows in UPSERT using system columns
  20. Postgres ERROR: could not open file for reading: Permission denied
  21. Can I use return value of INSERT…RETURNING in another INSERT?
  22. Changing a postgres containers server port in Docker Compose
  23. How do you find the disk size of a Postgres / PostgreSQL table and its indexes
  24. no pg_hba.conf entry for host
  25. PostgreSQL authentication method 10 not supported
  26. How to delete an enum type value in postgres?
  27. Why unsigned integer is not available in PostgreSQL?
  28. now() default values are all showing same timestamp
  29. What’s the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)
  30. JPA and PostgreSQL with GenerationType.IDENTITY

Leave a Comment