ERROR: operator does not exist: numeric = character varying
LINE 67: JOIN UNITIME_SESSION us2 ON us2.UNIQUEID = ss3.SESSION_ID

-- 使用CAST標(biāo)準(zhǔn)語(yǔ)法
SELECT * FROM numeric_table n
JOIN varchar_table v ON n.id = CAST(v.id AS NUMERIC);

-- 使用PostgreSQL特有操作符
SELECT * FROM numeric_table n
JOIN varchar_table v ON n.id = v.id::NUMERIC;

-- 創(chuàng)建雙向隱式轉(zhuǎn)換（需超級(jí)用戶權(quán)限）
CREATE CAST (numeric AS varchar) WITH INOUT AS IMPLICIT;
CREATE CAST (varchar AS numeric) WITH INOUT AS IMPLICIT;

-- 類型權(quán)限配置
ALTER TYPE numeric OWNER TO <用戶名>;
ALTER TYPE varchar OWNER TO <用戶名>;

-- 查詢現(xiàn)有轉(zhuǎn)換
SELECT 
  c1.typname AS source_type,
  c2.typname AS target_type,
  t.castcontext
FROM pg_cast t
JOIN pg_type c1 ON c1.oid = t.castsource
JOIN pg_type c2 ON c2.oid = t.casttarget;

-- 刪除冗余轉(zhuǎn)換
DROP CAST (varchar AS numeric);
DROP CAST (numeric AS varchar);

-- 查看多匹配轉(zhuǎn)換
SELECT * FROM pg_cast 
WHERE castsource::regtype IN ('numeric', 'varchar') 
  AND casttarget::regtype IN ('numeric', 'varchar');

-- 查詢隱式類型轉(zhuǎn)換配置
select 
	c1.typname as "castsource",
	c2.typname as "casttarget",
	t.castcontext,
	t.castmethod
from pg_cast as t
LEFT JOIN pg_type c1 on c1.oid=t.castsource
LEFT JOIN pg_type c2 on c2.oid=t.casttarget
WHERE c1.typname = 'varchar'