Configure case insensitivity for database object names to match Oracle capitalization rules - PolarDB

When migrating Oracle SQL to PolarDB for PostgreSQL (Compatible with Oracle), queries that reference identifiers in uppercase (as Oracle stores them) fail because PolarDB stores unquoted identifiers in lowercase — the two databases fold in opposite directions. The case insensitivity feature bridges this gap: with it enabled, unquoted names, all-uppercase quoted names, and all-lowercase quoted names are treated as equivalent, so Oracle SQL runs on PolarDB without rewriting identifiers.

How identifier folding works

Oracle and PolarDB handle unquoted identifiers differently:

Database	Unquoted identifier	Stored as
Oracle	`test_table`	`TEST_TABLE`
PolarDB (PostgreSQL)	`test_table`	`test_table`

Both databases store double-quoted identifiers exactly as written. This means a table created as test_table in Oracle becomes TEST_TABLE in storage, but the same statement in PolarDB produces test_table — and a query using "TEST_TABLE" then fails on PolarDB because test_table and TEST_TABLE are not the same.

The case insensitivity feature resolves this by making the following three identifier forms equivalent:

Identifier form	Example	Equivalent when feature is on
Unquoted	`test_table`	Yes
Double-quoted, all uppercase	`"TEST_TABLE"`	Yes
Double-quoted, all lowercase	`"test_table"`	Yes
Double-quoted, mixed case	`"TEST_table"`	No — stored and matched exactly

When enabled, all four of these queries return the same result:

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

Mixed-case double-quoted names such as "TEST_table" are not affected. The feature applies only when all letters in a double-quoted name are uniformly uppercase or uniformly lowercase.

Supported objects

Case insensitivity applies to all of the following object types. You cannot enable it for individual object types — it applies to all or none.

Databases
Schemas
Tables, including common table expressions (CTEs), indexes, views, and materialized views
Columns
Aliases
Functions (revision version 2.0.14.26.0 or later)
Packages

Enable or disable case insensitivity

Set the polar_case_sensitive_for_columnref parameter to control the feature:

Value	Behavior
`on`	Case insensitivity is enabled
`off`	Case insensitivity is disabled

Note

Case insensitivity is enabled by default for clusters created after minor version 1.1.24 (released July 2022).

Via the console: Go to your cluster's parameter settings and update polar_case_sensitive_for_columnref.

Via SQL:

-- Enable
SET polar_case_sensitive_for_columnref = on;

-- Disable
SET polar_case_sensitive_for_columnref = off;

Usage notes

Before enabling on existing clusters:

For clusters created before minor version 1.1.24 (July 2022), check whether any databases, schemas, tables, or columns use names that differ only in case (for example, a column named id and another named ID). Enabling the feature on such clusters can return incorrect query results because the engine can no longer distinguish between those names.

Avoid ambiguous object names:

Avoid naming objects where the only difference between names is letter case. When a query involves tables that have columns with the same letters in different cases, use <table_name>.<column_name> notation or column aliases.

Warning

Enabling case insensitivity on a cluster that already has objects with names differing only in case can cause DDL statements to affect unintended objects. For example, if a table named "tbl" already exists and you later create "TBL", running DROP TABLE "TBL" deletes both tables because "tbl" and "TBL" are treated as equivalent.

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

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

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)

Mixed-case names are not normalized

The feature does not normalize mixed-case double-quoted names. Object names that are double-quoted and consist of both lowercase and uppercase letters are not affected by this feature:

CREATE TABLE "TEST_table"(id int);
SELECT * FROM "TEST_table" WHERE "ID" = 10;