Обсуждение: BUG #2390: check constraint
The following bug has been logged online: Bug reference: 2390 Logged by: Andreas Kretschmer Email address: andreas.kretschmer@schollglas.com PostgreSQL version: 8.1.3 Operating system: Debian Linux Description: check constraint Details: i want to add a check constraint like: create table foo (i char(7) CHECK (i ~ '^[0-9]{6,7}$')); i doesn't work, but if works, if i change the type for i to varchar(7). Bug or feature?
On Wed, 12 Apr 2006, Andreas Kretschmer wrote: > The following bug has been logged online: > > Bug reference: 2390 > Logged by: Andreas Kretschmer > Email address: andreas.kretschmer@schollglas.com > PostgreSQL version: 8.1.3 > Operating system: Debian Linux > Description: check constraint > Details: > > i want to add a check constraint like: > > create table foo (i char(7) CHECK (i ~ '^[0-9]{6,7}$')); > > i doesn't work, but if works, if i change the type for i to varchar(7). Well, the regex doesn't entirely make sense for char(n) data. It's not possible to have 6 characters between beginning and end because it's a fixed length 7 character string. If you try to insert '000000' into i, you're actually inserting '000000 ' which is invalid by the constraint.
Stephan Szabo <sszabo@megazone.bigpanda.com> writes: > On Wed, 12 Apr 2006, Andreas Kretschmer wrote: >> i want to add a check constraint like: >> create table foo (i char(7) CHECK (i ~ '^[0-9]{6,7}$')); >> >> i doesn't work, but if works, if i change the type for i to varchar(7). > Well, the regex doesn't entirely make sense for char(n) data. It's not > possible to have 6 characters between beginning and end because it's a > fixed length 7 character string. If you try to insert '000000' into i, > you're actually inserting '000000 ' which is invalid by the constraint. You could argue that since we consider trailing spaces not to be semantically significant in char(n), it would be more consistent to strip those spaces before performing the regex match. Currently the system goes out of its way to cause the trailing spaces in the char(n) value to be seen by the regex: there's actually a separate ~ operator for bpchar. If we simply removed that, and let the normal char-to-text promotion be invoked first, the match would work as Andreas expects. I seem to recall that we've discussed this before, but don't remember if the idea was actively rejected or just faded out of mind without being implemented. regards, tom lane
On Thu, 13 Apr 2006, Tom Lane wrote: > Stephan Szabo <sszabo@megazone.bigpanda.com> writes: > > On Wed, 12 Apr 2006, Andreas Kretschmer wrote: > >> i want to add a check constraint like: > >> create table foo (i char(7) CHECK (i ~ '^[0-9]{6,7}$')); > >> > >> i doesn't work, but if works, if i change the type for i to varchar(7). > > > Well, the regex doesn't entirely make sense for char(n) data. It's not > > possible to have 6 characters between beginning and end because it's a > > fixed length 7 character string. If you try to insert '000000' into i, > > you're actually inserting '000000 ' which is invalid by the constraint. > > You could argue that since we consider trailing spaces not to be > semantically significant in char(n), it would be more consistent to > strip those spaces before performing the regex match. Possibly, although I'm not sure that the particulars of how we treat spaces in char(n) are precisely right either. :) AFAIR, the spec doesn't talk about stripping spaces, it talks about padding shorter values. That's usually the same, but for cases like this one, I think it's different.