domain+enum

Lists: pgsql-general
From: Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: domain+enum
Date: 2008-12-07 19:28:10
Message-ID: 2f4958ff0812071128q608a52d0uad4c427fc866d129@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

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


From: Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: domain+enum
Date: 2009-01-12 11:47:24
Message-ID: 2f4958ff0901120347vfa98ca9gb300d4d4ecf9151@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

Tom, will you guys consider changing that for 8.4 ? I know there was a
discussion about that on hackers, and everyone pretty much agreed it
would be nice to fix it - so it works as expected.

thanks.