SQL identifiers case-sensitivity

Handling case-sensitivity with different database engines.

In general, database engines use case-sensitive object identifiers. In most cases, when you do not specify identifiers in double quotes, the SQL parser automatically converts names to uppercase or lowercase. With this rule, identifiers will match when the objects are also created without double quoted identifiers.

For example, when creating the following table in ORACLE DB:

CREATE TABLE Customer ( cust_ID INTEGER )

With the above SQL, ORACLE creates a table named "CUSTOMER" with a "CUST_ID" column. Since DB object identifiers are converted to uppercase when executing SQL statements, the next SELECT statement will match the "CUSTOMER" table name and "CUST_ID" column name:

SELECT * FROM customer WHERE CUST_ID = 219

However, the next statements will fail:

SELECT * FROM "customer" WHERE "CUST_ID" = 219

The next table shows the behavior of each database engine regarding case sensitivity and double quoted identifiers:

Table 1. Database server support of case sensitivity and double-quoted identifiers
Database Server Type	Un-quoted names	Double-quoted names
IBM® DB2® LUW	Converts to uppercase	Yes
IBM Informix®	Converts to lowercase	Needs DELIMIDENT environment variable
Microsoft™ SQL Server	Not converted, kept as is	Needs `SET QUOTED_IDENTIFIER ON` (native delimiters are `[ ]` brackets)
Oracle® MySQL / MariadDB	Not converted, kept as is	Needs `ANSI_QUOTES` SQL mode
Oracle Database Server	Converts to uppercase	Yes
PostgreSQL	Converts to lowercase	Yes
SAP HANA®	Converts to uppercase	Yes
SQLite	Not converted, kept as is	Warning: Case insensitive!

Important:

When DB object identifiers are case sensitive and are not converted to uppercase or lowercase when not using double-quotes, it is possible to create two tables with a similar name (mind the uppercase C in the table name):

CREATE TABLE customer ( cust_id INTEGER )  -- first table 
CREATE TABLE Customer ( cust_id INTEGER )  -- second table

For maximum protability, define database object names in lowercase and do not use double quoted identifiers.

When using Static SQL statements, the fglcomp compiler converts table and column names to lowercase when not using single quotes or double quotes. Check the output of fglcomp -S option.