Search Postgresql Archives

Re: Domains

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx so that your
      message can get through to the mailing list cleanly





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux