Re: How to add month.year column validation

----- "Dann Corbit" <DCorbit@connx.com> wrote:

> CREATE DOMAIN Nasty_Month_year AS CHAR(7)
> CHECK ( SUBSTR(VALUE, 1, 2) IN ('01', '02', '03', '04', '05', '06',
> '07', '08', '09', '10', '11', '12') AND SUBSTR(VALUE, 3, 1) = '.' AND
> SUBSTR(VALUE, 4, 4)::int BETWEEN 1980 and 2110 );
>
> CREATE TABLE foo (bar Nasty_Month_year );
>
> This Works:
> INSERT INTO foo VALUES ('02.1997');
>
> This:
> INSERT INTO foo VALUES ('13.1997');
> Gives this:
> ERROR:  value for domain nasty_month_year violates check constraint
> "nasty_month_year_check"
>
> ********** Error **********
>
> ERROR: value for domain nasty_month_year violates check constraint
> "nasty_month_year_check"
> SQL state: 23514
>

My attempt at this problem:


test=# \d date_check
          Table "public.date_check"
  Column  |         Type         | Modifiers
----------+----------------------+-----------
 id       | integer              |
 date_fld | character varying(7) |
Check constraints:
    "date_check_date_fld_check" CHECK (isfinite(replace(date_fld::text, '.'::text, '/01/'::text)::date) AND
length(date_fld::text)= 7) 

test=# INSERT INTO date_check VALUES (1,'12.2009');
INSERT 0 1

test=# INSERT INTO date_check VALUES (1,'13.2009');
ERROR:  date/time field value out of range: "13/01/2009"
HINT:  Perhaps you need a different "datestyle" setting.

test=# INSERT INTO date_check VALUES (1,'12.09');
ERROR:  new row for relation "date_check" violates check constraint "date_check_date_fld_check"

test=# SELECT * from date_check ;
 id | date_fld
----+----------
  1 | 12.2009
(1 row)


Adrian Klaver
aklaver@comcast.net