Re: How to add month.year column validation

----- "Dann Corbit" <DCorbit(at)connx(dot)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(at)comcast(dot)net