Dear Pavel,
Thank you. Now it is not possible to use separate columns. The same works at MYSQL with out casting. But here it is so. We have used CAST and works fine.
Thank you
vikram
From: Pavel Stehule <pavel.stehule@xxxxxxxxx>
To: Vikram A <vikkiatbipl@xxxxxxxx>
Cc: PGSQL - Genearal <pgsql-general@xxxxxxxxxxxxxx>
Sent: Wednesday, 17 August 2011 6:00 PM
Subject: Re: Type casting text to Numeric - Query Error
Hello
2011/8/17 Vikram A <vikkiatbipl@xxxxxxxx>:
> 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
