I've got a column that ought to have 3 possible values (for instance 'A', 'B', 'C'). It's conventional to make use of enum for such type of things, consider many tables during my DB may have such column, I must define a default value ('C') for type, representing this enum.
(AFAIK with no additional definitions, I have to write something similar to this:
%COLUMN% %ENUM_TYPE% NOT NULL DEFAULT enum_first(null::%ENUM_TYPE%),
each time i want such enum)
CREATE TYPE ... statement doesn't let it define default value, but
CREATE DOMAIN ... does.
I attempted just a little trick:
CREATE TYPE zzz_enum AS ENUM (...); CREATE DOMAIN zzz AS zzz_enum DEFAULT 'A';, but query with comparison of
%zzz column% = 'A' leads to error:
... operator %zzz% = undefined ...
How do i define comparison operator for domain 'by-hand' or produce a custom type, that will:
- Have a listing of possible values, that we could cope with query
- Have among individuals values like a default value
- Be considered a identifier, that we can use like every regular SQL key in table definition
Thanks ahead of time!
This must work, however it does not. I believe it is a bug.
Stick to the discussion here: http://archives.postgresql.org/pgsql-bugs/2011-01/msg00082.php
CREATE FUNCTION zzz_like (a zzz_enum, b TEXT) RETURNS BOOLEAN AS $$ SELECT $1::TEXT LIKE $2; $$ LANGUAGE 'sql' CREATE OPERATOR ~~ (LEFTARG=zzz_enum, RIGHTARG=TEXT, PROCEDURE=zzz_like) SELECT * FROM zzz_test WHERE zzz_column LIKE '%A%'
You might just cast the expression to
TEXT in your query:
SELECT * FROM zzz_test WHERE zzz_column::TEXT LIKE '%A%'
Sorry, got the issue wrong.
As @Peter pointed out, it is a bug indeed, since domain names over complex types like
enum aren't unconditionally cast into
anyenum needed for that corresponding equality operators.