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.
精彩评论