How to add month.year column validation

Hi Andrus,

How to add column validation to table column which forces this ?

You're looking for a constraint - I presume you know what conditions you want to check for - I've done a similar thing recently ensuring the first character in a column is a decimal point, or that if one column's value is something, another column's value is within a certain range etc.

http://www.postgresql.org/docs/8.4/static/ddl-constraints.html (assuming you're running 8.4)

Regards,
Andy

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Andrus
Sent: Tuesday, December 22, 2009 12:03 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How to add month.year column validation

Database contains CHAR(7) type column which must be NOT NULL and must
be in
the format

mm.yyyy

where:

mm - month number, always two digits in range 01 .. 12

. - separator must be point always.

yyyy - must be four digits in range approx. 1980 .. 2110 .

How to add column validation to table column which forces this ?

If you use a date (IOW, just always use a day of 1) then error detection
is automatic. It also only consumes 4 bytes of storage.

You can also use a check constraint, or create a domain.
http://www.postgresql.org/docs/8.4/static/ddl-constraints.html
http://www.postgresql.org/docs/current/static/sql-createdomain.html

On Dec 22, 2009, at 15:03 , Andrus wrote:

Database contains CHAR(7) type column which must be NOT NULL and
must be in the format

mm.yyyy

where:

mm - month number, always two digits in range 01 .. 12

. - separator must be point always.

yyyy - must be four digits in range approx. 1980 .. 2110 .

How to add column validation to table column which forces this ?

A check constraint with regex might work, something like (untested)

CHECK (val ~ $re$^(19|20|21)[0-9]{2}.[01][0-9]$$re$)

However, I strongly recommend using a date column with, perhaps, a
restriction that the day field is always 1 or some other agreed-upon
(and documented) value (e.g., CHECK (val = date_truc('month', val))).
If the data is date data, you're likely going to want to do other
operations on the field which will be much easier if it's already a
date value.

Michael Glaesemann
grzm seespotcode net

Michael,

Thank you very much.
I have very few knowledge on rexexps.

CHECK (val ~ $re$^(19|20|21)[0-9]{2}.[01][0-9]$$re$)

1. I tried

create temp table test5 ( tmkuu char (7) CHECK (tmkuu ~
$re$[01][0-9].^(19|20)[0-9]{2}$re$) ) on commit drop;
insert into test5 values('01.2009');

but got error

ERROR: new row for relation "test5" violates check constraint
"test5_tmkuu_check"

2. How to restrict month numbers to range 01 .. 12 ?
This regex seems to accept month numbers like 13

3. How to add this check to existing column for 8.0 and later servers ?
I tried

alter table test5 alter tmkuu add CHECK (tmkuu ~
$re$[01][0-9].^(19|20)[0-9]{2}$re$ )

causes error:

ERROR: syntax error at or near "add"

However, I strongly recommend using a date column with, perhaps, a
restriction that the day field is always 1 or some other agreed-upon (and
documented) value (e.g., CHECK (val = date_truc('month', val))). If the
data is date data, you're likely going to want to do other operations on
the field which will be much easier if it's already a date value.

This is existing database and many application are using it.
I cannot change column type to date since other applications are expecting
char(7) column.

As far as I know it is not possible to makse such change so that externally
it is visible and writeable as char(7) column for 8.0+ servers.

Andrus.

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

On Tue, Dec 22, 2009 at 1:47 PM, Andrus <kobruleht2@hot.ee> wrote:

Michael,

Thank you very much.
I have very few knowledge on rexexps.

CHECK (val ~ $re$^(19|20|21)[0-9]{2}.[01][0-9]$$re$)

1. I tried

create temp table test5 ( tmkuu char (7) CHECK (tmkuu ~
$re$[01][0-9].^(19|20)[0-9]{2}$re$) ) on commit drop;
insert into test5 values('01.2009');

but got error

ERROR:  new row for relation "test5" violates check constraint
"test5_tmkuu_check"

Yeah, you've got some characters in odd places there (^ in particular,
$ missing at end.) Here's one that pretty much works:

create table test (a text check (a ~
$r$^(0[1-9]|1[0-2]).(19[89][0-9]|20[0-9]{2}|210[0-9]|2110)$$r$));

2. How to restrict month numbers to range 01 .. 12 ?
This regex seems to accept month numbers like 13

Make one that doesn't accept 13.

3.  How to add this check to existing column for 8.0 and later servers ?
I tried

alter table test5 alter tmkuu add CHECK (tmkuu ~
$re$[01][0-9].^(19|20)[0-9]{2}$re$  )

causes error:

ERROR:  syntax error at or near "add"

Left out a keyword, "constraint"

alter table test add constraint test_a_check check (a ~
$r$^(0[1-9]|1[0-2]).(19[89][0-9]|20[0-9]{2}|210[0-9]|2110)$$r$);

However, I strongly recommend using a date column with, perhaps, a
restriction that the day field is always 1 or some other agreed-upon  (and
documented) value (e.g., CHECK (val = date_truc('month', val))).  If the
data is date data, you're likely going to want to do other  operations on
the field which will be much easier if it's already a  date value.

This is existing database and many application are using it.
I cannot change column type to date since other applications are expecting
char(7) column.

Just so you know, down this road lies madness. But you can make it
work. I've been down this path before too. Eventually your
developers doing dba work will paint you into a corner.

On Dec 22, 2009, at 16:11 , Scott Marlowe wrote:

alter table test add constraint test_a_check check (a ~
$r$^(0[1-9]|1[0-2]).(19[89][0-9]|20[0-9]{2}|210[0-9]|2110)$$r$);

However, I strongly recommend using a date column with, perhaps, a
restriction that the day field is always 1 or some other agreed-
upon (and
documented) value (e.g., CHECK (val = date_truc('month', val))).
If the
data is date data, you're likely going to want to do other
operations on
the field which will be much easier if it's already a date value.

This is existing database and many application are using it.
I cannot change column type to date since other applications are
expecting
char(7) column.

Just so you know, down this road lies madness.

I completely agree. Schedule some downtime and make the column a date
column.

Michael Glaesemann
grzm seespotcode net

----- "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

Dann,

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 );

Thank you. This looks better than regexp since it conforms to SQL standard.
regexps are PostgreSql specific.
I created test script to test changing existing database column with
possibly wrong data:

CREATE DOMAIN MonthYear AS CHAR(7) NOT NULL
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 2100 );

CREATE TABLE foo (tmkuu char(7));

-- add bad data
INSERT INTO foo VALUES ('');
INSERT INTO foo VALUES ('02.x');
INSERT INTO foo VALUES ('02.1970');
INSERT INTO foo VALUES ('02.2101');
INSERT INTO foo VALUES (NULL);
-- add good data
INSERT INTO foo VALUES ('12.2009');

delete from foo where tmkuu is null OR NOT ( SUBSTR(tmkuu, 1, 2) IN ('01',
'02', '03', '04', '05', '06',
'07', '08', '09', '10', '11', '12') AND SUBSTR(tmkuu, 3, 1) = '.' AND
SUBSTR(tmkuu, 4, 4)::int BETWEEN 1980 and 2100 );

alter table foo alter tmkuu type MonthYear;
select * from foo;

but got error on DELETE:

ERROR: invalid input syntax for integer: "x"

How to apply this constraint to existing data ?

Andrus.

Andrus,

-- add bad data
INSERT INTO foo VALUES ('');
INSERT INTO foo VALUES ('02.x');
INSERT INTO foo VALUES ('02.1970');
INSERT INTO foo VALUES ('02.2101');
INSERT INTO foo VALUES (NULL);
-- add good data
INSERT INTO foo VALUES ('12.2009');

delete from foo where tmkuu is null OR NOT ( SUBSTR(tmkuu, 1, 2) IN ('01', '02', '03', '04', '05', '06',
'07', '08', '09', '10', '11', '12') AND SUBSTR(tmkuu, 3, 1) = '.' AND
SUBSTR(tmkuu, 4, 4)::int BETWEEN 1980 and 2100 );

alter table foo alter tmkuu type MonthYear;
select * from foo;

but got error on DELETE:

ERROR: invalid input syntax for integer: "x"

How to apply this constraint to existing data ?

Remove the data that doesn't conform to the constraint.

You got the error because you're trying to cast SUBSTR(tmkuu, 4, 4) to an integer in your DELETE statement - but in the case of the second record, that expression cannot be an integer (because of the x) hence the error.

Regards,
Andy

On Wed, Dec 23, 2009 at 12:02 PM, Andy Shellam
<andy-lists@networkmail.eu> wrote:

Andrus,

-- add bad data
INSERT INTO foo VALUES ('');
INSERT INTO foo VALUES ('02.x');
INSERT INTO foo VALUES ('02.1970');
INSERT INTO foo VALUES ('02.2101');
INSERT INTO foo VALUES (NULL);
-- add good data
INSERT INTO foo VALUES ('12.2009');

delete from foo where tmkuu is null OR NOT ( SUBSTR(tmkuu, 1, 2) IN ('01', '02', '03', '04', '05', '06',
'07', '08', '09', '10', '11', '12') AND SUBSTR(tmkuu, 3, 1) = '.' AND
SUBSTR(tmkuu, 4, 4)::int BETWEEN 1980 and 2100 );

alter table foo alter tmkuu type MonthYear;
select * from foo;

but got error on DELETE:

ERROR:  invalid input syntax for integer: "x"

How to apply this constraint to existing data ?

Remove the data that doesn't conform to the constraint.

You got the error because you're trying to cast SUBSTR(tmkuu, 4, 4) to an integer in your DELETE statement - but in the case of the second record, that expression cannot be an integer (because of the x) hence the error.

You can use the regex I posted to get rid of the data easily, then go
back to the substr one for a check constraint after that.

Scott,

You can use the regex I posted to get rid of the data easily, then go
back to the substr one for a check constraint after that.

regex is non-standard.
How to implement this in standard SQL ?

Andrus.

On Thursday 24 December 2009 3:47:23 pm Andrus wrote:

Scott,

You can use the regex I posted to get rid of the data easily, then go
back to the substr one for a check constraint after that.

regex is non-standard.
How to implement this in standard SQL ?

Andrus.

Why should it matter? The initial data clean up is a one time event. Once the
fields are cleaned up the check constraint will keep them that way.

--
Adrian Klaver
aklaver@comcast.net

On Thu, Dec 24, 2009 at 4:47 PM, Andrus <kobruleht2@hot.ee> wrote:

Scott,

You can use the regex I posted to get rid of the data easily, then go
back to the substr one for  a check constraint after that.

regex is non-standard.
How to implement this in standard SQL ?

I take it you need a way to scrub your data in various databases, not
just pgsql? I'm not sure there is a simple SQL standard way. It's
likely that this one time job might require various non-standard ways
of scrubbing your data this one time. You're gonna have to figure
out how to make databases other than pgsql happy without me, the only
one I'm even a little familiar with is Oracle, and my oracle-fu is
rather rusty after a three year or so lay off from it.