sql - 错误 : operator does not exist with custom domain

Question

我在 Postgres 12 数据库的架构中有声明:

CREATE TYPE something_e AS ENUM('OPEN', 'CLOSE');
CREATE DOMAIN something_t AS something_e NOT NULL DEFAULT 'OPEN';
CREATE TABLE xxx (
...
something something_t,
...
);

而且 INSERT 似乎工作正常。但是选择 - 不会:

INSERT INTO yyy (...) VALUES (..., (SELECT ID FROM xxx WHERE ... AND something='OPEN'));

这里我得到一个错误:

"ERROR: operator does not exist: something_t = unknown".

我尝试将类型转换为 something_e，使用 :: 转换为 something_t 但没有成功。我什至收到类似“运算符不存在:something_t = something_t”的错误!我最初的想法是为了减少代码的重复，所以在枚举something_e的基础上引入了这个something_t新域，但是现在如何使用这样的标准来选择呢？ INSERT 似乎自动将“OPEN”字符串转换为 something_t...这种情况下正确的语法是什么？

Answer 1

您不能直接将字符串与自定义类型的值进行比较:

select * from xxx where something = 'OPEN';
-- ERROR: operator does not exist: something_t = unknown

您不能对自定义类型使用相等运算符:

select * from xxx where something = 'OPEN'::something_t;
-- ERROR: operator does not exist: something_t = something_t

不过，您可以将类型值转换为字符串，然后进行比较:

select * from xxx where something::text = 'OPEN'

Demo on DB Fiddle