Re: check (constraint) on point data type?

[Michael Glaesemann <grzm(at)seespotcode(dot)net>]

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