----- "Dann Corbit" <dcor...@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 akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general