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

From Iain
Subject Re: A quick question about domains
Date
Msg-id 00a201c3b3df$e84b8c50$7201a8c0@mst1x5r347kymb
Whole thread Raw
In response to A quick question about domains  ("Iain" <iain@mst.co.jp>)
List pgsql-admin
 > > 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


pgsql-admin by date:

Previous
From: "Andrei Bintintan"
Date:
Subject: Re: Sensitive sort problem?
Next
From: Jeff Boes
Date:
Subject: Vacuum stats interpreted?