Re: check constraint on char field
От | Scott Marlowe |
---|---|
Тема | Re: check constraint on char field |
Дата | |
Msg-id | 1086806303.23855.38.camel@localhost.localdomain обсуждение исходный текст |
Ответ на | check constraint on char field (Robert Fitzpatrick <robert@webtent.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: