Skip site navigation (1)
Skip section navigation (2)
## Re: check (constraint) on point data type?

### In response to

### Responses

### pgsql-novice by date

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

- check (constraint) on point data type? at 2007-07-24 19:59:14 from Jill

- Re: check (constraint) on point data type? at 2007-07-24 21:10:57 from Jim Nasby

Next: From:Jim NasbyDate:2007-07-24 21:10:57Subject: Re: check (constraint) on point data type?Previous: From: Jim AdamsDate: 2007-07-24 20:38:27Subject: Re: check (constraint) on point data type?