Re: Trying to update a box data type column

[Michael Fuhr <mike@xxxxxxxx>]

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