Re: check (constraint) on point data type?
От | Jim Nasby |
---|---|
Тема | Re: check (constraint) on point data type? |
Дата | |
Msg-id | 87ED613D-5445-4E09-9387-653C62BF9B8F@decibel.org обсуждение исходный текст |
Ответ на | Re: check (constraint) on point data type? (Michael Glaesemann <grzm@seespotcode.net>) |
Ответы |
Re: check (constraint) on point data type?
|
Список | 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)
В списке pgsql-novice по дате отправления: