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

Overview

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 tbl(id int);

select * from "TBL" where "ID" = 10;
    In Oracle, when the preceding CREATE TABLE statement is executed, the table name tbl 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 TBL table will return an error that indicates failure to find the table. 
  • create table "TBL"("ID" int);

select * from TBL 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:
create table "TBL"("ID" int);

create table TBL(ID int);

create table tbl(id int);

create table "tbl"("id" int);

create table Tbl (Id int);
Note Object names that are double-quoted and consist of both lowercase and uppercase letters are not affected by this feature. Examples:
create table "Tbl" ("Id" int);

select * from "Tbl" where "Id" = 1;

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

Limits

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
Note Other objects, such as functions and packages, are not supported by the case insensitivity feature.