Case-insensitive comparison of identifiers

Details

Detail name Value
Changelog Number 25492
Type New Feature
Status Resolved
Fix Versions Exasol 2025.1.3, Exasol 2025.2.0
Resolution Date 2025-09-17

Background

Some customers want to write queries in which identifiers are not case-sensitive. More specifically, they want the comparison of identifiers to be case-insensitive but the case of identifiers to be preserved, for example for result column names.

This is useful for compatibility with SQL from some other databases, especially Databricks.

New feature

  • There is a new system value, SQL_IDENTIFIER_COMPARISON. The default value CASE SENSITIVE makes comparison of identifiers case-sensitive. The value IGNORE CASE makes comparison of identifiers case-insensitive.
  • If you expect to change the system value in future, we recommend that you refer to objects from view or scripts using their exact name in IGNORE CASE mode and do not create objects whose names differ only by case in CASE SENSITIVE mode, because otherwise some views or scripts might then stop working. See the documentation on identifiers for more details.
  • "ROWNUM" is a special column identifier for the row number, even when quoted. The user can create a column called "rownum" (or any other case variation). The capitalized spelling is still a special column even when SQL_IDENTIFIER_COMPARISON is set to IGNORE CASE.

The feature applies to tables, columns, schemas and most other database objects (including connections, scripts, …). It does not apply to

  • Passwords and consumer group names which are always case-sensitive.
  • User and role names which are always case-insensitive.

Examples

alter system set SQL_IDENTIFIER_COMPARISON = 'CASE SENSITIVE';
create table "MyTable" (C int);

-- Observed: Success
select * from "MyTable";

-- Observed: Error 'object "mytable" not found'
select * from "mytable";

alter system set SQL_IDENTIFIER_COMPARISON = 'IGNORE CASE';

-- Observed: Success
select * from "MyTable";
-- Observed: Success
select * from "mytable";

alter system set SQL_IDENTIFIER_COMPARISON = 'CASE SENSITIVE';
create table "MYTABLE" (C int);

-- Observed: Success
select * from "MyTable";

alter system set SQL_IDENTIFIER_COMPARISON = 'IGNORE CASE';

-- Observed: Error 'the matching objects "MyTable" and "MYTABLE" are ambiguous because of case-insensitive comparison'
select * from "MyTable";