One thing I don't understand, can someone please explain that to me:
(that's on 8.4, but it "works" same way on 8.3)
CREATE TYPE rcount AS ENUM (
'one',
'two',
'three'
);
CREATE DOMAIN foocount AS rcount DEFAULT 'one' NOT NULL;
CREATE DOMAIN foostamp AS bigint NOT NULL DEFAULT (EXTRACT(epoch FROM
timeofday()::timestamp)*1000000)::bigint CHECK (VALUE > 0);
now:
gj=# create table footest(a int not null, b rcount default 'one' not null);
CREATE TABLE
gj=# insert into footest(a) select generate_series(1,100);
INSERT 0 100
gj=# update footest set b = 'three' where random() < 0.5;
UPDATE 37
gj=# update footest set b = 'two' where random() > 0.5;
UPDATE 41
gj=# select count(*) from footest where b = 'three';
count
-------
23
(1 row)
Works perfectly well, with enums.
Now, trying to do the same thing, with enum in domain:
gj=# create table footest(a int not null, b foocount, c foostamp);
CREATE TABLE
gj=# insert into footest(a) select generate_series(1,100);
INSERT 0 100
gj=# update footest set b = 'two' where random() > 0.5;
UPDATE 45
gj=# update footest set b = 'three' where random() < 0.5;
UPDATE 47
gj=# select count(*) from footest where b = 'three';
ERROR: operator does not exist: foocount = unknown
LINE 1: select count(*) from footest where b = 'three';
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
Now, I could understand that - if that was the problem with domains in
general, but :
gj=# select count(*) from footest where c < 1234;
count
-------
0
(1 row)
Please, can someone explain that strange behavior to me ? I do
consider it a buggy one, to be honest.
--
GJ