PolarDBPostgreSQL版的大小寫不敏感功能 - 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類對象）
列名
別名
函數名
同義字

說明

包等其他資料庫物件暫時不支援大小寫不敏感模式。
函數對象在核心小版本1.1.42及以上版本支援大小寫不敏感模式。
不支援為以上資料庫物件單獨開啟大小寫不敏感模式。

大小寫不敏感功能受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)