ERROR: operator does not exist with custom domain

user_1980863 0
Viewed 663

I have declarations in my schema for Postgres 12 database:

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,
...
);

And it seems that INSERTs work fine. But the select - does not:

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

And here I get an error:

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

I tried to cast a type to something_e, to something_t with :: but without any success. I even got an error like "operator does not exist: something_t = something_t" ! My initial idea was to reduce repeating of a code, so to introduce this something_t new domain based on the enum something_e, but how to select now using such a criteria? INSERT's seems cast 'OPEN' strings to something_t automatically... What is the right syntax for this case?

sql-insert where-clause postgresql sql
2 Answers

You can't compare a string to a value of a custom type directly:

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

You can't use the equality operator on a custom type:

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

You can, however, cast the type value to a string, then compare:

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

Demo on DB Fiddle

user_106767160

What you need is a type cast, so that PostgreSQL's type resolution rules can find the suitable operator.

That can either be an explicit cast:

... WHERE CAST(something AS text) = 'OPEN'

or you create an implicit type cast:

CREATE CAST (something_e AS text) WITH INOUT AS IMPLICIT;

Then you can use your query without an explicit cast.

user_64643080
Related