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 Case sensitive
IBM Informix® (1) Converts to lowercase Syntax disallowed (non-ANSI mode)
Microsoft™ SQL Server (2) Not converted, kept as is Case sensitive
Oracle® MySQL / MariadDB Not converted, kept as is Syntax disallowed
Oracle Database Server Converts to uppercase Uppercase
PostgreSQL Converts to lowercase Lowercase
SAP HANA® Converts to uppercase Case sensitive
SQLite Not converted, kept as is Case insensitive

(1) If not ANSI database mode.

(2) When case-sensitive charset/collation used.

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.
Note: 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.