Re: A quick question about domains - Mailing list pgsql-admin

On Wed, Nov 26, 2003 at 10:19:50 +0900,
  Iain <iain@mst.co.jp> wrote:
> Hi Bruno,
>
> > > I want to use domains in the project I'm working on right now. I'd like
> to
> > > hear from anyone with experience of using them. Basically I'd like to
> know
> > > if you think it's a good idea, and whether there are any pitfalls that I
> > > should be aware of. I did a quick search on the lists and just came up
> with
> > > a few old bugs, apparently fixed. It's a fairly big job to get the
> domains
> > > in place, so I'd hate to regret it afterwards.
> >
> > It is going to be hard to answer this without knowing more about how you
> > are planning on using them.
> >
> > You probably do want to be running 7.4 to use them. In 7.3 there weren't
> > domain specific check constraints other than NULL and NOT NULL.
>
> The test/development DBs are now 7.4 (or will be from today).
>
> I did some testing and was satisfied that I could drop and re-create check
> constraints without affecting existng data, it even checked that the data
> fitted the new check constraints.
>
> Principally I want to use domains to standardise data type definitions for
> all master table primary keys, price, money field and things like boolean
> flags, status fields and their defaults and checks.
>
> For example:
>
> create domain kaiin_status_cd_dom as char(1) not null default 'A' constraint
> kaiin_status_check check (VALUE in ('A', 'S', 'D'));
>
> create domain flg0_dom as char(1) not null default '0' constraint flag_check
> check (VALUE in ('0', '1')) ;

That seems to be a pretty reasonable use of domains.

>
> I read about some (potential?) problems regarding use of db_dump and
> domains, but I'm not sure of the status of these as of 7.4.

I don't remember that problem, but it should be easy to make sure your
stuff works before going too far.

>
> Also, this is a rework of an existing system and dates are stored as
> strings. I don't like it but I'm stuck with it for now. I was hoping to be
> able to provide some DB level validation of the dates as 'YYYY-MM-DD' but I
> havn't come across a neat way to do it yet. I also hope that domains might
> facilitate a smooth change to date type dates someday in the future (in my
> dreams, i guess). In the mean time, if anyone knows a 'neat' way to validate
> the date strings I'd be happy to hear about it. I got bogged down in very
> much over involved (an probably slow) code that couldn't guarantee a valid
> date anyway. The date conversion function to_date is far too forgiving to
> fulfill this purpose, it seems.

You can use a check constraint with a regular expression. It would look
something like like:
check (datecolumnname ~ '^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]$')
That just checks for digits in the right place, it doesn't tightly
bound the range of the numbers.