PostgreSQL 自定義運算符 UUID 到 varchar

我有一個相當複雜的 Postgres 數據庫，其中許多 UUID 欄位不正確地儲存為 VARCHAR。我想逐步遷移它們，但不幸的是，這樣做破壞了我的所有觀點，因為 Postgres 沒有內置的varchar = uuid. 寧可重寫我的所有視圖或嘗試一次大規模遷移，我想臨時創建一個 uuid = varchar 運算符，直到遷移完成。

我以前從未創建過自定義運算符，並且我在下面的嘗試不起作用：

CREATE OR REPLACE FUNCTION uuid_equal_varchar (varchar, uuid)
RETURNS boolean AS 'SELECT $1::text = $2::text;' LANGUAGE sql IMMUTABLE;

CREATE OPERATOR = (
   leftarg = character varying,
   rightarg = uuid,
   procedure = uuid_equal_varchar,
   commutator = =
);

然而，這個運營商打破了一切。包括一個簡單的 varchar = varchar 比較（見下文）：

SELECT * FROM test WHERE pk_test = '123';
ERROR:  invalid input syntax for uuid: "123"

有人可以向我解釋我做錯了什麼嗎？我是否正在嘗試一些不可能的事情？

你要做的CREATE CAST不是操作員。這就是問題：

SELECT pg_typeof(uuid), uuid = uuid::varchar AS eq
FROM gen_random_uuid() AS t(uuid);
ERROR:  operator does not exist: uuid = character varying
LINE 1: SELECT pg_typeof(uuid), uuid = uuid::varchar FROM gen_random...
                                    ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

所以我們需要創建一個CAST. 這會在需要時將 varchar 提升為 uuid。如果你真的想的話，你可以走另一條路。如果您這樣做，您需要創建演員表（uuid AS 文本）。類型系統不知道 varchar：我們不在 PostgreSQL 中使用它；它本質text上具有類型無關緊要的長度約束，因此速度較慢。

CREATE CAST (varchar AS uuid)
 WITH INOUT
 AS IMPLICIT;

現在你可以再試一次。

pg_typeof | eq
-----------+----------
uuid      | t
(1 row)

以供參考，

• IMPLICIT

表示可以在任何上下文中隱式呼叫強制轉換。

• INOUT

指示強制轉換是 I/O 轉換強制轉換，通過呼叫源數據類型的輸出函式並將結果字元串傳遞給目標數據類型的輸入函式來執行。

也就是說，當底層類型發生變化時，必須重新創建所有視圖，

CREATE TABLE foo(uuid)
AS
 VALUES (gen_random_uuid()::varchar);

CREATE VIEW bar AS TABLE foo;

現在我們嘗試改變類型foo

ALTER TABLE foo
 ALTER uuid
 SET DATA TYPE uuid;
ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view bar depends on column "uuid"

那失敗bar了，所以我們放棄更改類型並重新創建它，

BEGIN;
 DROP VIEW bar;
 ALTER TABLE foo ALTER uuid SET DATA TYPE uuid;
 CREATE VIEW bar AS TABLE foo;
COMMIT;

我們有快樂。

\d bar;
    View "public.bar"
Column | Type | Modifiers 
--------+------+-----------
uuid   | uuid | 

引用自：https://dba.stackexchange.com/questions/177733