Re: Domains

On Feb 19, 2006, at 2:12 , Stephan Szabo wrote:

> On Sat, 18 Feb 2006, Peter wrote:
>
>> Hello,
>>
>> I am migrating to postgresql from another database. I want to take
>> advantage of using domains. Let's suppose I create domain
>> 'email'(varchar 128). Then I change my mind and want to increase all
>> columnst that have type 'emaill' to varchar(255). How do I change the
>> domain 'email' to the new datatype.

As Stephan pointed out, I don't believe there's a general way to do
this. However, if something you're looking to use domains for is to
check length of text, you may want to implement this as a check
constraint on the domain. This check constraint can then be altered
in the future using alter domain. For example:

test=# create domain email as text
constraint assert_maximum_length check (length(value) <= 128);
CREATE DOMAIN
test=# create table accounts
(
account_id serial primary key
, email email not null unique
);
NOTICE: CREATE TABLE will create implicit sequence
"accounts_account_id_seq" for serial column "accounts.account_id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"accounts_pkey" for table "accounts"
NOTICE: CREATE TABLE / UNIQUE will create implicit index
"accounts_email_key" for table "accounts"
CREATE TABLE
test=# insert into accounts (email) values ('this is a very very very
very very very very very very very very very very very very very very
long text string that is not actually a valid email address but will
serve for this example that is just checking for length');
ERROR: value for domain email violates check constraint
"assert_maximum_length"
test=# insert into accounts (email) values ('this is a very very very
very very very very very very very very very very very very very very
long text string');
INSERT 0 1
test=# alter domain email drop constraint assert_maximum_length;
ALTER DOMAIN
test=# alter domain email add constraint assert_maximum_length check
(length(value) <= 256);
ALTER DOMAIN
test=# insert into accounts (email) values ('this is a very very very
very very very very very very very very very very very very very very
long text string that is not actually a valid email address but will
serve for this example that is just checking for length');
INSERT 0 1

This more flexible technique can be used for more general situations
too, such as checking format with a regex match.

Michael Glaesemann
grzm myrealbox com