postgres: overload/add comparison operators to domain

I have a column which should have 3 possible values (for example 'A', 'B', 'C'). It is conventional to use enum for such kind of things, but since many tables in my DB will have such column, I would like to define a default value ('C') for type, representing this enum.

(AFAIK without any additional definitions, I need to write something like this:

%COLUMN% %ENUM_TYPE% NOT NULL DEFAULT enum_first(null::%ENUM_TYPE%),

every time i need such enum)

CREATE TYPE ..开发者_如何学JAVA. statement does not allow to define default value, but CREATE DOMAIN ... does.

I tried 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' results in error:

... operator %zzz% = undefined ...

How can I define comparison operator for domain 'by-hand' or create a custom type, which will:

• Have a list of possible values, which I could get through query
• Have one of those values as a default value
• Be a identifier, which I could use like any regular SQL type in table definition

Thanks in advance!

This ought to work, but it doesn't. I think it's a bug.

Follow 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 may just cast the expression to TEXT right in your query:

SELECT  *
FROM    zzz_test
WHERE   zzz_column::TEXT LIKE '%A%'

Update:

Sorry, got the problem wrong.

As @Peter mentioned, it's a bug indeed, since domains over complex types like array and enum are not implicitly cast into anyarray and anyenum required for the corresponding equality operators.