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

What's wrong with (NOT location <@ box '((0,0),(1,1))') ?

On Jul 24, 2007, at 2:06 PM, Michael Glaesemann wrote:

>
> 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
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)