SEO

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



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

>
> 'email'(varchar 128). Then I change my mind and want to increase all
> columnst that have type 'emaill' to varchar(255).



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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

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

---------------------------(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/i...atedomain.html

Michael Glaesemann
grzm myrealbox com




---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster