Post: PostgreSQL database object name is case sensitive

Document directory

• 9.2.2. Case sensitivity of the identifier

From: http://bbs.chinaunix.net/viewthread.php? Tid = 812899

 

This problem is lost because I just got started with pgsql.

PostgreSQL is case-sensitive and converts the database object name in SQL statements to lowercase by default. Therefore, if you specify a name that is case-insensitive when creating a database object, when you access these objects through SQL statements, you must use double quotation marks (") to enclose database objects, prompting PostgreSQL not to convert the object name to lowercase letters, otherwise, the exception "XXXXX object does not exist" will be triggered. For example, your database has a table named Tuser, you must use a query statement similar to this in the graphical query tool provided by PostgreSQL to correctly execute: Select * from "Tuser ", of course, it is not case sensitive to the reserved words and keywords in the SQL standard, so it is completely possible to write the statement as select * from "Tuser.

In addition, PostgreSQL is case sensitive to data, which is different from sqlserver (sqlserver is not sensitive by default). For example, the Tuser table contains the field name, there is a record with the name field value "Tony Tang ".

Select * from "Tuser" where "name" like '% Tony % ';
This record cannot be found, but you can write it as follows:

Select * from "Tuser" where upper ("name") like '% Tony % ';

Oh, do you think this is not very nice, and you are worried that the performance will be affected? Fortunately, PostgreSQL provides the keyword Ilike to help us solve this problem. This is really a very interesting keyword (I like). For the first method, you only need to replace like with Ilike.

Finally, we recommend that you use single quotes as the String constant identifier when writing SQL scripts, although double quotes and single quotes in the MS sqlserver can be used as the identifier of a String constant, however, PostgreSQL strictly distinguishes this feature from each other (double quotation marks cannot be used as the identifier of a String constant). To be secure, we recommend that you use double quotation marks (") for all database object names (") explicit conventions to ensure strict matching of write size.

 

 

 

 

 

Similar to PostgreSQL, the MySQL documentation provides the following instructions:

 

9.2.2. Case sensitivity of the identifier

In MySQL, the database corresponds to the directory in the data directory. Each table in the database corresponds to at least one file in the database directory (or multiple, depending on the storage engine ). Therefore, the case sensitivity of the operating system determines the case sensitivity of the Database Name and table name. This indicates that the database name and table name are case sensitive in most Unix systems, but not in windows. A notable exception is Mac OS X, which is UNIX-based but uses the default file system type (HFS +) and is not case sensitive. However, Mac OS X also supports UFS volumes, which are case sensitive, just like UNIX. For more information, see section 1.8.4, "MySQL extension to standard SQL ".

Note:Although database names and table names on some platforms are not case sensitive, different cases should not be used in the same query to reference a given database or table. The following query does not work because it references tables my_tables and as my_tables at the same time:

mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;

Columns, indexes, storage subroutines, and trigger names are case-insensitive and column aliases are not sensitive.

By default, table aliases are case sensitive in UNIX, but not in Windows or Mac OS X. The following query does not work in UNIX because it references aliases A and A at the same time:

mysql> SELECT col_name FROM tbl_name AS a

    -> WHERE a.col_name = 1 OR A.col_name = 2;

However, this query is available in windows. To avoid differences, we recommend that you use consistent conversions. For example, you can always create and reference the Database Name and table name in lower case. We recommend that you use this conversion for most porting and use cases.

In MySQL, if you want to store and use the table name and database name on the hard disk, it can be determined by the lower_case_tables_name system variable.Mysqld. Lower_case_tables_name can use any of the following values:

Value	Description
0	Store the table name and database name on the hard disk in both upper and lower case specified by the create table or create database statement. The name is case sensitive. This is the default setting in UNIX systems. Note that if you use -- lower-case-table-names = 0 in a case insensitive file system and use different cases to access the MyISAM table name, the index may be damaged.
1	The table name is saved in lower case on the hard disk. The table name is case sensitive. MySQL converts all table names to lowercase for storage and search. This behavior is also suitable for database names and table aliases. This value is the default value in Windows and Mac OS X systems.
2	The table name and database name are saved using the case specified by the create table or create database Statement on the hard disk, but MySQL converts them to lowercase for search. The name is case sensitive.Note:OnlyApplicable to case-insensitive file systems! The InnoDB table name is saved in lowercase, for example, lower_case_tables_name = 1.

In Windows and Mac OS X, the default value of lower_case_tables_name is 1.

If you only use MySQL on one platform, you do not need to change the lower_case_tables_name variable. However, if you want to transfer tables between platforms of file systems that are case sensitive, you may encounter difficulties. For example, in UNIX, my_tables and my_tables are two different tables, but in windows, the two tables have the same names. To avoid data transfer problems caused by the case sensitivity of the database or table name, you can use two options:

· Lower_case_tables_name = 1 can be used in any system. The disadvantage of using this option is that when show tables or show databases is used, it cannot be seen whether the name is in upper or lower case.

· Use lower_case_tables_name = 0 in UNIX and lower_case_tables_name = 2 in windows. In this way, the Case sensitivity of the Database Name and table name can be retained. The disadvantage is that the database name and table name are always referenced in the correct case in windows. If you transfer a query to Unix, since it is important to make it big and lowercase in UNIX, it does not work if the case is incorrect.

Exceptions: If you are using an InnoDB table, set lower_case_tables_name to 1 on any platform to forcibly convert the name to lowercase.

Please note that before setting lower_case_tables_name to 1 in UNIX, restartMysqldPreviously, you must first convert the old database name and table name to lowercase.