Thread: Type casting text to Numeric - Query Error

Hello

2011/8/17 Vikram A <vikkiatbipl@yahoo.in>:
> 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"
> I am getting error while selecting using some aggregation.
> 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.
> Can i have your suggestion/solutions please?

SELECT sum(CASE WHEN sampletext ~ e'^\\d+$' THEN sampletext::int ELSE
0 END) FROM ..

Regards

Pavel Stehule

p.s. better to use a two columns



>
> Regards,
> Vikram A

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@iol.ie