Re: Domains - Mailing list pgsql-general

Hi,

Thanks for the suggestion. However I just wanted to give a brief
description of something I want to achieve. I believe such feature will
be very useful in more complicated environments.

Kind regards,

Peter



Michael Glaesemann wrote:
>
> 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
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>
>