Problems with capitalization in PostgreSQL

Today, I have a strange problem (actually a very basic problem).

Problem Description:

A sequence, named: sv_info_seq_id in Pgadmin, the query information_schema.sequences view also exists, the name is exactly the same, but in the reset always prompt relation does not exists, the Reset statement is

Select Setval (' sv_info_seq_id ', 1)

Troubleshooting process:

Puzzled for a long time without fruit, the execution of select Nextval (' sv_info_seq_id ') is the same as the hint relationship does not exist. When you view the build, SQL only discovers the problem, and it is created using the Create SEQUENCE "sv_info_seq_id" ...

It suddenly occurred to me that PG is case sensitive, and by default the object names are all converted to lowercase and stored in the database. If you want to keep uppercase or case-insensitive, you must enclose the object in double quotation marks and use double quotation marks. Execute again

Select Setval (' "sv_info_seq_id" ', 1) gets the correct result.

As an example:

Create sequence "Test_seq" INCREMENT 1 start 1 minvalue 1 maxvalue 10;

After execution, the name stored in the database is uppercase, and the name must also be enclosed in double quotation marks, such as

Select Nextval ("Test_seq")

Final conclusion:

Database objects as far as possible all lowercase names, do not capitalize or size mix, development, good development specifications, will avoid problems.

This article is from the "Mirror" blog, please be sure to keep this source http://383133430.blog.51cto.com/454215/1775156

Problems with capitalization in PostgreSQL