Thread: A quick question about domains

On Tue, Nov 25, 2003 at 14:30:47 +0900,
  Iain <iain@mst.co.jp> wrote:
>
> 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.

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.

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')) ;

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.

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.

Regards
Iain

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

Nice idea, I hadn't thought of that. It's somewhat tidier than my original
experiment. I think it's about time I learnt a little more about regular
expressions. I'll check it out.

Thanks for your input, I appreciate it.

Regards
iain