Обсуждение: check (constraint) on point data type?

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

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)

[Please don't top post as it makes the discussion more difficult to
follow.]

On Jul 24, 2007, at 16:10 , Jim Nasby wrote:

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

Two reasons I didn't provide that answer:

1)

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

> 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.

2) I misread the specs and assumed that 0 and 1 values for x and y
were to be rejected. I suspected that the geometric functions were
inclusive rather than exclusive, which, upon experimentation, was
correct. Right answer to the wrong specs :)

# select not '(0,0)'::point <@ '((0,0),(1,1))'::box;
?column?
----------
f
(1 row)

Michael Glaesemann
grzm seespotcode net

----- Original Message -----
From: "Michael Glaesemann" <grzm@seespotcode.net>
To: "Jim Nasby" <decibel@decibel.org>
Cc: "Jill" <jill289@hotmail.com>; <pgsql-novice@postgresql.org>
Sent: Tuesday, July 24, 2007 5:19 PM
Subject: Re: [NOVICE] check (constraint) on point data type?


> [Please don't top post as it makes the discussion more difficult to
> follow.]
>
> On Jul 24, 2007, at 16:10 , Jim Nasby wrote:
>
>> What's wrong with (NOT location <@ box '((0,0),(1,1))') ?
>
> Two reasons I didn't provide that answer:
>
> 1)
>
> On Jul 24, 2007, at 2:06 PM, Michael Glaesemann wrote:
>
>> 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.
>
> 2) I misread the specs and assumed that 0 and 1 values for x and y  were
> to be rejected. I suspected that the geometric functions were  inclusive
> rather than exclusive, which, upon experimentation, was  correct. Right
> answer to the wrong specs :)
>
> # select not '(0,0)'::point <@ '((0,0),(1,1))'::box;
> ?column?
> ----------
> f
> (1 row)
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>

Thank you so much guys - quick, detailed, and to the point. In fact Michael
understood right what I was trying to say (i.e., I need to accept values
between 0 and 1 and reject anything else) - but Jim's suggestion is shorter
(but Michael's is useful since now I know how to write a struc for another
problem I have).
Thanks again!

On Jul 25, 2007, at 0:47 , Jill wrote:

> Thank you so much guys - quick, detailed, and to the point. In fact
> Michael understood right what I was trying to say (i.e., I need to
> accept values between 0 and 1 and reject anything else) - but Jim's
> suggestion is shorter (but Michael's is useful since now I know how
> to write a struc for another problem I have).

Glad it helped. Do note that using the <@ operator is inclusive,
i.e., (0,0) and (1,1) are included. The method using the
strict_during helper is exclusive, so (0,0) and (1,1) are rejected.
So the two constraints are slightly different. So make sure you use
the one which works for your situation :)

Michael Glaesemann
grzm seespotcode net