Обсуждение: BUG #2390: check constraint

Поиск
Список
Период
Сортировка

BUG #2390: check constraint

От
"Andreas Kretschmer"
Дата:
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?

Re: BUG #2390: check constraint

От
Stephan Szabo
Дата:
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.

Re: BUG #2390: check constraint

От
Tom Lane
Дата:
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

Re: BUG #2390: check constraint

От
Stephan Szabo
Дата:
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.