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

Re: BUG #2390: check constraint

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
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 17:09:03
Message-ID: 20060413100546.W83930@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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.


In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2006-04-13 18:30:52
Subject: Re: Permission denied on fsync / Win32 (was right sibling is not next child)
Previous:From: Raphael JacquotDate: 2006-04-13 17:02:58
Subject: BUG #2392: Feature request : point_ops

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