Re: check constraint on char field

On Wed, 2004-06-09 at 10:42, Robert Fitzpatrick wrote:
> I have a field in my pgsql 7.4.2 table that is char(6) type. This holds
> an ID that contains only numbers, but must be six characters in length.
> First two chars are the year followed by a sequential number. For
> instance, a number entered for the time this year would be '040001'. I
> was trying to setup a check constraint to make sure only number were
> used, this is what I have, but it is allowing alphanumerics:
>
> projectno >= '000000' AND projectno <= '999999'
>
> Or maybe just a trigger to create the number itself would be better?

You've chosen the wrong type.  char(6) will ALWAYS be 6 characters long,
no matter how short a string you try to put it in.  I.e. if you insert
'abc' it will become 'abc   ' on insert.  You also need to cast.  Try
this:

smarlowe=> create table test (id varchar(6) check (id::text::int>0 and
id::text::int<1000000 and length(id)=6));
CREATE TABLE
smarlowe=> insert into test values ('6');
ERROR:  new row for relation "test" violates check constraint "test_id"
smarlowe=> insert into test values ('601');
ERROR:  new row for relation "test" violates check constraint "test_id"
smarlowe=> insert into test values ('abc123');
ERROR:  invalid input syntax for integer: "abc123"
smarlowe=> insert into test values ('000123');
INSERT 17263 1