Re: Trying to update a box data type column - Mailing list pgsql-general

From Michael Fuhr
Subject Re: Trying to update a box data type column
Date
Msg-id 20060523173644.GA52042@winnie.fuhr.org
Whole thread Raw
In response to Trying to update a box data type column  (Yonatan Ben-Nes <yonatan@epoch.co.il>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Kenneth Downs
Date:
Subject: Re: background triggers?
Next
From: Tom Lane
Date:
Subject: Re: Trying to update a box data type column