Re: check (constraint) on point data type? - Mailing list pgsql-novice

On Jul 24, 2007, at 14:59 , Jill wrote:

> The field is of type 'point', and I'd like it to reject any values
> less than 0 or bigger than 1 (i.e., accept only points with values
> like (0.4, 0.26)).
> Let's say I try to define the upper boundary by doing:
> ALTER TABLE "public"."locations"  ADD CONSTRAINT "up_boundary_chk"
> CHECK (location < (1,1));

One issue is that point literals are quoted: '(1,1)', not (1,1).

However, I don't think your constraint would do quite what you think
it would.

Here's what I would do:

-- Define a helper function to determine if a float is during a
particular open interval:
CREATE FUNCTION strict_during(double precision, double precision,
double precision)
RETURNS BOOLEAN
STRICT
IMMUTABLE
LANGUAGE SQL AS $_$
SELECT $1 > $2 AND $1 < $3
$_$;


-- Note that the check constraint tests both the x and y values of
the point using the
-- strict_during helper
CREATE TABLE points
(
     a_point point not null
         check (strict_during(a_point[0], 0, 1) AND
                strict_during(a_point[1], 0, 1))
);

test=# INSERT INTO points (a_point) VALUES ('(-1,-1)'); -- should fail
ERROR:  new row for relation "points" violates check constraint
"points_a_point_check"
test=# INSERT INTO points (a_point) VALUES ('(-1,0.5)'); -- should fail
ERROR:  new row for relation "points" violates check constraint
"points_a_point_check"
test=# INSERT INTO points (a_point) VALUES ('(0.5,-1)'); -- should fail
ERROR:  new row for relation "points" violates check constraint
"points_a_point_check"
test=# INSERT INTO points (a_point) VALUES ('(0,0)'); -- should fail
ERROR:  new row for relation "points" violates check constraint
"points_a_point_check"
test=# INSERT INTO points (a_point) VALUES ('(0.5, 0.5)'); -- should
be ok
INSERT 0 1
test=# INSERT INTO points (a_point) VALUES ('(1,0.5)'); -- should fail
ERROR:  new row for relation "points" violates check constraint
"points_a_point_check"
test=# INSERT INTO points (a_point) VALUES ('(0.5, 1)'); -- should fail
ERROR:  new row for relation "points" violates check constraint
"points_a_point_check"
test=# INSERT INTO points (a_point) VALUES ('(10,0.5)'); -- should fail
ERROR:  new row for relation "points" violates check constraint
"points_a_point_check"
test=# INSERT INTO points (a_point) VALUES ('(0.5, 10)'); -- should fail
ERROR:  new row for relation "points" violates check constraint
"points_a_point_check"
test=# select * from points;
   a_point
-----------
(0.5,0.5)
(1 row)

I haven't looked at the geometric functions closely enough to see if
you could use some of those rather than defining your own helper, but
this should work.

Hope that helps.

Michael Glaesemann
grzm seespotcode net