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%'

Update:

Sorry, got the issue wrong.

As @Peter pointed out, it is a bug indeed, since domain names over complex types like array and enum aren't unconditionally cast into anyarray and anyenum needed for that corresponding equality operators.