On Thu, Dec 24, 2009 at 4:47 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 ?
I take it you need a way to scrub y
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 mat
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes
On Wed, Dec 23, 2009 at 12:02 PM, Andy Shellam
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
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 n
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
- "Dann Corbit" 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 Nast
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
On Tue, Dec 22, 2009 at 1:47 PM, Andrus 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
"nasty_month_year_check"
** Error **
ERROR: value for domain nasty_month_year violates check constraint
"nasty_month_year_check"
SQL state: 23514
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow
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 er
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@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 valida
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.
where:
mm - month number, always two digits in range 01 .. 12
. - separator must be point always.
- must be four digits in range approx. 1980 ..
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
Database contains CHAR(7) type column which must be NOT NULL and must be in
the format
mm.
where:
mm - month number, always two digits in range 01 .. 12
. - separator must be point always.
- must be four digits in range approx. 1980 .. 2110 .
How to add column validation to table
15 matches
Mail list logo
