Re: Type casting text to Numeric - Query Error

On 17/08/2011 13:17, Vikram A wrote:
> Hi there,
>
> I have the following definitions,
>
> 1. CREATE TABLE Sampletemp ( Sampleid serial NOT NULL, SampleText
> character varying(50), CONSTRAINT Sampletemp_id PRIMARY KEY (Sampleid));
>
> 2. Insert into Sampletemp (SampleText) values ('Mr. Raja'),('Mr.
> Alex'),('1000'),('2500'),('555');
>
> 3. select sum(SampleText) as SampleText from Sampletemp;
>
> ERROR: function sum(character varying) does not exist
> LINE 3: select sum(SampleText) as SampleText from Sampletemp;
> ^
> HINT: No function matches the given name and argument types. You may
> need to add explicit type casts.
>
> 4. select sum(SampleText :: int) as SampleText from Sampletemp;
> ERROR: invalid input syntax for integer: "Mr. Raja"

Recent versions of PostgreSQL are much pickier about types - certain
implicit casts were removed, most notably text to numeric types.

In any case, SUM makes no sense for character values. You'd be better
off using two separate columns for the text and numeric values.

> I would like to sum up these values, if it has TEXT (example Name)that
> can be ZERO while querying. I need answer as 4055.

Try using a CASE expression inside the SUM to test for non-numeric values.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie