The following bug has been logged online:

Bug reference: 2390
Logged by: Andreas Kretschmer
Email address: andreas.kretschmer (AT) schollglas (DOT) 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?

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Wed, 12 Apr 2006, Andreas Kretschmer wrote:

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.


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

Stephan Szabo <sszabo (AT) megazone (DOT) bigpanda.com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Thu, 13 Apr 2006, Tom Lane wrote:

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.


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly