Re: BUG #2390: check constraint

On Thu, 13 Apr 2006, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)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.