check constraint on char field

[Robert Fitzpatrick]

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?

--
Robert
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Scott Marlowe]

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<1 000000 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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Scott Marlowe]

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<1 000000 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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster