配置大小写不敏感以兼容Oracle数据库对象名-云原生数据库 PolarDB-阿里云

本文介绍了PolarDB PostgreSQL版（兼容Oracle）的大小写不敏感功能。

背景

当前由于Oracle和PolarDB PostgreSQL版（兼容Oracle）对于数据库对象名称的大小写处理方式不同，在不加双引号的情况下，Oracle将对象名转为大写存储，PolarDB PostgreSQL版（兼容Oracle）将对象名转为小写存储，使用双引号时则不做转换，因此部分Oracle用户使用PolarDB PostgreSQL版（兼容Oracle）时可能出现大小写行为不一致的问题，例如：

```
CREATE TABLE test_table(id int);
SELECT * FROM "TEST_TABLE" WHERE "ID" = 10;
```
上述SQL语句在Oracle中执行时test_table表名在系统视图中默认大写存储，因此查询语句可以正常执行，而PolarDB PostgreSQL版（兼容Oracle）由于默认小写存储，查询语句执行时则会报错不存在表“TEST_TABLE”表。
```
CREATE TABLE "TEST_TABLE"(id int);
SELECT * FROM test_table WHERE "ID" = 10;
```
上述SQL语句在PolarDB PostgreSQL版（兼容Oracle）中执行也会出现无法匹配表名的问题。

因此，PolarDB PostgreSQL版（兼容Oracle）推出大小写不敏感功能，解决从Oracle迁移到PolarDB PostgreSQL版（兼容Oracle）频繁出现的大小写问题。开启大小写不敏感功能后，对于无双引号、双引号+全大写或双引号+全小写的数据库对象被视为等价。例如，以下4种查询语句在查询结果上是等价的：

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;

说明

双引号+大小写交错的对象名不受影响。例如：

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

注意事项

内核小版本（V1.1.24）（发布时间：2022年7月）之前创建的集群，当无法确保数据库中不存在同名不同大小写的数据库对象（database、schema、table、column）时，请勿手动开启大小写不敏感功能，否则可能导致查询不正确。
为每个对象赋予合理且有区分度的名称，尽量避免出现同名不同大小写的情况。
当查询涉及的多张表中有列出现同名不同大小写时，尽量以<表名.列名>或别名的方式引用，避免查询解析时出现列引用混淆。
执行DDL操作时需要注意操作的对象名。例如，对于大小写不敏感且在数据库相同模式下不可能同时存在"tbl"表和"TBL"表，因此如下语句可以成功执行。
```
CREATE TABLE "tbl" (id int);
DROP TABLE "TBL";
```

功能介绍

创建和使用以下数据库对象时，无双引号、双引号+全大写和双引号+全小写名称具有相同的结果，双引号+大小写交错名不受影响。

数据库名
模式名
表名（包括cte、索引、视图、物化视图等relation类对象）
列名
别名
函数名
包名

说明

函数对象在内核小版本2.0.14.26.0及以上版本支持大小写不敏感模式。
不支持为以上数据库对象单独开启大小写不敏感模式。

大小写不敏感功能受polar_case_sensitive_for_columnref参数控制，您可以通过SQL语句开启或关闭大小写不敏感功能，取值如下：

on：开启大小写不敏感功能。
off：关闭大小写不敏感功能。

说明

内核小版本（V1.1.24）（发布时间：2022年7月）之后创建的集群，默认开启大小写不敏感功能。

使用示例

表名、列名以及别名

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

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

返回结果如下：

 id
----
 10
(1 row)

数据库名、模式名和函数名

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;

返回结果如下：

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

包名

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;

返回结果如下：

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