Case insensitivity in PolarDB for PostgreSQL - PolarDB - Alibaba Cloud Documentation Center

Background

Oracle and PolarDB for PostgreSQL (Compatible with Oracle) handle the case of database object names differently. Without double quotes, Oracle converts object names to uppercase for storage, while PolarDB for PostgreSQL (Compatible with Oracle) converts them to lowercase. When double quotes are used, no conversion is performed. As a result, Oracle users who use PolarDB for PostgreSQL (Compatible with Oracle) may encounter case inconsistency issues. For example:

```
CREATE TABLE test_table(id int);
SELECT * FROM "TEST_TABLE" WHERE "ID" = 10;
```
When the preceding SQL statement is executed in Oracle, the table name test_table is stored in uppercase in system views by default, so the query runs normally. However, because PolarDB for PostgreSQL (Compatible with Oracle) stores names in lowercase by default, the query fails with an error that the table "TEST_TABLE" does not exist.
```
CREATE TABLE "TEST_TABLE"(id int);
SELECT * FROM test_table WHERE "ID" = 10;
```
The preceding SQL statement also causes a table name mismatch when executed in PolarDB for PostgreSQL (Compatible with Oracle).

To address this issue, PolarDB for PostgreSQL (Compatible with Oracle) provides the case insensitivity feature to resolve frequent case-related issues when migrating from Oracle to PolarDB for PostgreSQL (Compatible with Oracle). After you enable case insensitivity, database objects without double quotes, with double quotes and all uppercase names, or with double quotes and all lowercase names are treated as equivalent. For example, the following four 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

Object names that are double-quoted with mixed case are not affected. For example:

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

Usage notes

For clusters created before minor version 1.1.24 (released in July 2022), do not enable case insensitivity if you cannot confirm that no database objects (databases, schemas, tables, or columns) have names that differ only in case. Otherwise, queries may return incorrect results.
Assign meaningful and distinctive names to each object to avoid names that differ only in case.
When a query involves multiple tables that have columns with names that differ only in case, reference columns by using <table_name>.<column_name> or aliases to avoid column reference ambiguity during query parsing.
When performing DDL operations, pay attention to the object names. For example, with case insensitivity enabled, a "tbl" table and a "TBL" table cannot coexist in the same schema. Therefore, the following statements can be executed successfully:
```
CREATE TABLE "tbl" (id int);
DROP TABLE "TBL";
```

Features

When you create and use the following database objects, names without double quotes, with double quotes and all uppercase, and with double quotes and all lowercase produce the same result. Double-quoted names with mixed case are not affected.

Database names
Schema names
Table names, including CTEs, indexes, views, and materialized views
Column names
Aliases
Function names
Synonyms

Note

Other database objects such as packages do not support case insensitivity.
Functions support case insensitivity in minor version 1.1.42 and later.
You cannot enable case insensitivity for individual object types listed above.

Case insensitivity is controlled by the polar_case_sensitive_for_columnref parameter. You can use SQL statements to enable or disable this feature. Valid values:

on: enables case insensitivity.
off: disables case insensitivity.

Note

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

Examples

Table names, column names, 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;

The following result is returned:

 id
----
 10
(1 row)

Database names, schema names, and function names

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;

The following result is returned:

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

Package names

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;

The following result is returned:

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