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