Trying to update a box data type column

On Tue, May 23, 2006 at 07:13:08PM +0200, Yonatan Ben-Nes wrote:

UPDATE treecategory SET box =
'('||"nleft"||','||"nright"||'),('||"nleft"||','||"nright"||')';
ERROR: column "box" is of type box but expression is of type text
HINT: You will need to rewrite or cast the expression.

And if I try to cast type text to box I get:
ERROR: cannot cast type text to box

Try this:

UPDATE treecategory SET box = box(point(nleft, nright), point(nleft, nright));

A hackish way to convert types where no cast exists is to use the
source and destination types' I/O functions:

UPDATE treecategory SET box = box_in(textout( <text expression> ));

You could create a function that implicitly does the above conversion:

CREATE FUNCTION box(text) RETURNS box AS $$
BEGIN
RETURN $1;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

UPDATE treecategory SET box = box( <text expression> );

If you need to perform such conversions regularly then you could
use CREATE CAST to create a cast between the types.

--
Michael Fuhr

Yonatan Ben-Nes <yonatan@epoch.co.il> writes:

I can't figure out how I can update the box column using the values at
the nleft & nright columns.

Use the provided constructor functions:

regression=# select box(point(1,2),point(3,4));
box
-------------
(3,4),(1,2)
(1 row)

As a general rule, converting something to text and back is not the way
to convert data from one type to another; if it's not a really
outlandish conversion, there'll be a function or cast to help.

regards, tom lane