Case insensitivity - PolarDB - Alibaba Cloud Documentation Center

This topic describes the case insensitivity feature of PolarDB for PostgreSQL (Compatible with Oracle).

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. Therefore, some Oracle users who are new to PolarDB for PostgreSQL (Compatible with Oracle) may encounter issues due to the difference in user habits. Examples:

```
CREATE TABLE test_table(id int);
SELECT * FROM "TEST_TABLE" WHERE "ID" = 10;
```
In Oracle, when the preceding CREATE TABLE statement is executed, the table name test_table is automatically converted to TBL. The statement that specifies to query data from the TBL table can be normally executed. However, when the CREATE TABLE statement is executed in PolarDB for PostgreSQL (Compatible with Oracle), the table name tbl is stored in lower case. Therefore, the SELECT statement that involves the TEST_TABLE table will return an error that indicates failure to find the table.
```
CREATE TABLE "TEST_TABLE"(id int);
SELECT * FROM test_table WHERE "ID" = 10;
```
The preceding query will also fail to execute in PolarDB for PostgreSQL (Compatible with Oracle) due to capitalization issues.

To solve the capitalization issues and facilitate the switch from Oracle to PolarDB for PostgreSQL (Compatible with Oracle), PolarDB for PostgreSQL (Compatible with Oracle) provides the case insensitivity feature. When this feature is enabled, the following object names are considered equivalent: object names that are not double-quoted, object names that are double-quoted and written in uppercase letters, and object names that are double-quoted and written in lowercase letters. For example, the names of the tables and columns created by each of the following statements are considered equivalent in terms of results:

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 and consist of both lowercase and uppercase letters are not affected by this feature. Examples:

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

Usage notes

You can set the polar_case_sensitive_for_columnref parameter to one of the following values in the console to enable or disable the case insensitivity feature:
- on: The case insensitivity feature is enabled.
- off: The case insensitivity feature is disabled.
Note
By default, the case insensitivity feature is enabled for clusters that are created after the minor version 1.1.24 is released in July 2022.
For clusters that are created before the minor version 1.1.24 is released in July 2022, if you cannot determine whether the databases, schemas, tables, or columns in the clusters use names that contain the same letters in different cases, do not enable the case insensitivity feature. Otherwise, incorrect query results are returned.
We recommend that you do not specify object names that contain the same letters in different cases.
If a query involves tables that have columns whose names contain the same letters in different cases, we recommend that you name the columns in the <Table name.Column name> format or use the aliases of the columns.
After you enable this feature for existing clusters on which tables have been created, we recommend that you proceed with caution when you execute DDL statements on these clusters. For example, assume that you enable the case insensitivity feature for a cluster on which a table named "tbl" already exists. Then, you execute the following CREATE TABLE statement to create a table named "TBL". When you execute the DROP TABLE statement to delete the "TBL" table, both tables are deleted because tables named "tbl" and "TBL" are considered equivalent.
```
CREATE TABLE "tbl" (id int);
DROP TABLE "TBL";
```

Feature description

The case insensitivity feature applies only to names of the following objects:

Databases
Schemas
Tables, including relational objects such as common table expressions (CTEs), indexes, views, and materialized views
Columns
Aliases
Functions
Synonyms

Note

Other objects, such as packages, are not supported by the case insensitivity feature.
Functions are supported by the case insensitivity feature for clusters whose revision version is 1.1.42 or later.
You cannot enable the case insensitivity feature for the preceding objects separately.

You can set the polar_case_sensitive_for_columnref parameter to one of the following values by executing SQL statements to enable or disable the case insensitivity feature:

on: The case insensitivity feature is enabled.
off: The case insensitivity feature is disabled.

Note

By default, the case insensitivity feature is enabled for clusters that are created after the minor version 1.1.24 is released in July 2022.

Examples

For 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;

Sample result:

 id
----
 10
(1 row)

For 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;

Sample result:

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

For 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;

Sample result:

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