From: Pavel Stehule <pavel.stehule@gmail.com>
To: Vikram A <vikkiatbipl@yahoo.in>
Cc: PGSQL - Genearal <pgsql-general@postgresql.org>
Sent: Wednesday, 17 August 2011 6:00 PM
Subject: Re: [GENERAL] 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