Configure case insensitivity for database object names to ensure Oracle compatibility - PolarDB

Oracle folds unquoted identifiers to uppercase; PolarDB for PostgreSQL (Compatible with Oracle) folds them to lowercase. This means that after migration, SQL statements that mix quoted uppercase names with unquoted references to the same object will fail. The case insensitivity feature resolves this by treating unquoted names and their all-uppercase or all-lowercase quoted equivalents as identical — so migrated DDL and DML statements work without modification.

Background

By default, Oracle and PolarDB for PostgreSQL (Compatible with Oracle) have different capitalization rules for the names of database objects. For object names that are not double-quoted, Oracle converts them to uppercase letters while PolarDB for PostgreSQL (Compatible with Oracle) converts them to lowercase letters. If an object name is double-quoted in the statement that creates the object, the name is stored as is. Examples of failures that arise from this difference:

When CREATE TABLE test_table(id int) is executed in Oracle, the table name is automatically converted to uppercase and stored as TEST_TABLE. The following SELECT succeeds in Oracle but fails in PolarDB for PostgreSQL (Compatible with Oracle) because the table name is stored in lowercase:
```
CREATE TABLE test_table(id int);
SELECT * FROM "TEST_TABLE" WHERE "ID" = 10;
```
The reverse scenario also fails in PolarDB for PostgreSQL (Compatible with Oracle):
```
CREATE TABLE "TEST_TABLE"(id int);
SELECT * FROM test_table WHERE "ID" = 10;
```

How it works

When case insensitivity is enabled, PolarDB for PostgreSQL (Compatible with Oracle) treats the following as equivalent:

Unquoted object names (for example, test_table)
Double-quoted names written entirely in uppercase (for example, "TEST_TABLE")
Double-quoted names written entirely in lowercase (for example, "test_table")

All four of the following queries refer to the same table:

SELECT * FROM test_table WHERE "ID" = 10;
SELECT * FROM "TEST_TABLE" WHERE "ID" = 10;
SELECT * FROM TEST_TABLE WHERE "ID" = 10;
SELECT * FROM "test_table" WHERE "ID" = 10;

Note

Double-quoted names that mix uppercase and lowercase letters are not affected. "TEST_table" is treated as a distinct, case-sensitive name regardless of this setting.

Supported objects

Case insensitivity applies to the following object types:

Object type	Notes
Databases
Schemas
Tables	Includes common table expressions (CTEs), indexes, views, and materialized views
Columns
Aliases
Functions	Requires revision version 1.1.42 or later
Synonyms

Note

Packages and other object types are not supported. The setting applies cluster-wide — you cannot enable it for individual objects.

Enable or disable case insensitivity

Set the polar_case_sensitive_for_columnref parameter to control the feature:

Value	Effect
`on`	Enables case insensitivity
`off`	Disables case insensitivity

Configure this parameter from the console or by running a SQL statement.

Note

Case insensitivity is enabled by default on clusters created after minor version 1.1.24 was released in July 2022.

Risks and recommendations for existing clusters

Risk: incorrect query results

Trigger condition: The cluster was created before minor version 1.1.24 (July 2022) and contains object names that use the same letters in different cases.

If you cannot verify that all database, schema, table, and column names use consistent casing, do not enable this feature. Enabling it on a cluster with same-letter names in different cases can return incorrect query results.

Risk: DDL statements may delete multiple objects

Trigger condition: Case insensitivity is enabled on a cluster that already has tables with names differing only in case.

Because "tbl" and "TBL" are treated as equivalent, the following sequence deletes both tables:

CREATE TABLE "tbl" (id int);
DROP TABLE "TBL";  -- Deletes both "tbl" and "TBL"

After enabling this feature on a cluster that already has tables, proceed with caution when running DDL statements.

Naming recommendations

Avoid object names that use the same letters in different cases.
If a query joins tables that have columns sharing the same letters in different cases, reference columns using the <table_name>.<column_name> format or use column aliases to remove ambiguity.

Examples

Tables, columns, and aliases

CREATE TABLE "TEST_TABLE"(id int);
INSERT INTO test_table VALUES(10);

SELECT "T".id FROM "TEST_TABLE" AS t WHERE "ID" = 10;

Result:

 id
----
 10
(1 row)

Databases, schemas, and functions

CREATE DATABASE test_database;
\c test_database
CREATE SCHEMA "TEST_SCHEMA";
CREATE FUNCTION "TEST_SCHEMA"."TEST_FUNCTION"(IN i int) RETURNS int AS $$ BEGIN RETURN i; END; $$ LANGUAGE plpgsql;
SELECT "TEST_DATABASE".test_schema.test_function(10) FROM dual;

Result:

 test_function
---------------
            10
(1 row)

Packages (not supported)

Packages are not supported by the case insensitivity feature:

CREATE PACKAGE "TEST_PACKAGE" AS
    FUNCTION test_function(i int) RETURN int;
END;

CREATE PACKAGE BODY "TEST_PACKAGE" AS
    FUNCTION test_function(i int) RETURN int
    IS
    BEGIN
        RETURN i;
    END;
END;

SELECT test_package."TEST_FUNCTION"(100) FROM dual;

Result:

 TEST_FUNCTION
---------------
           100
(1 row)