Thread: Domains

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. I can not figure how to do it with
"alter domain" syntax.

Thanks in advance for your help :-)

Kind regards,

Peter

I do not know about the word "domains" in this usage.

But all your problems with "varchar(x)" for any values of x are solved by just using type "text"

Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607

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. I can not figure how to do it with
> "alter domain" syntax.

It doesn't look like alter domain currenly has type changing support, so I
don't think you can do this (in general) right now.  Some conversions
might be possible with direct alterations to system tables, but that's a
bit dangerous.  I don't know if anyone's working on this right now either,
but it sounds reasonable (now that we have table column type changing).

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

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
>
>
>

On Feb 18, 2006, at 20:46 , Harald Armin Massa wrote:

> I do not know about the word "domains" in this usage.

http://www.postgresql.org/docs/8.1/interactive/sql-createdomain.html

Michael Glaesemann
grzm myrealbox com