From: | "Iain" <iain(at)mst(dot)co(dot)jp> |
---|---|
To: | "Bruno Wolff III" <bruno(at)wolff(dot)to> |
Cc: | <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: A quick question about domains |
Date: | 2003-11-26 05:41:15 |
Message-ID: | 00a201c3b3df$e84b8c50$7201a8c0@mst1x5r347kymb |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-11-26 05:53:27 | Re: Size on Disk |
Previous Message | Bruno Wolff III | 2003-11-26 05:23:13 | Re: A quick question about domains |