Skip site navigation (1) Skip section navigation (2)

Re: BUG #2390: check constraint

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
Cc: Andreas Kretschmer <andreas(dot)kretschmer(at)schollglas(dot)com>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2390: check constraint
Date: 2006-04-13 16:43:16
Message-ID: 21030.1144946596@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
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.  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

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2006-04-13 16:51:15
Subject: Re: BUG #2389: function within function return value
Previous:From: Peter BrantDate: 2006-04-13 16:10:15
Subject: Re: right sibling is not next child

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group