Re: Type casting text to Numeric - Query Error

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Vikram A <vikkiatbipl(at)yahoo(dot)in>
Cc: PGSQL - Genearal <pgsql-general(at)postgresql(dot)org>
Subject: Re: Type casting text to Numeric - Query Error
Date: 2011-08-17 12:30:31
Message-ID: CAFj8pRAy-E71OyZHhOaFyaPfgh-NqaSRxPYPVMJQaZWcX=k3gQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello

2011/8/17 Vikram A <vikkiatbipl(at)yahoo(dot)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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Raymond O'Donnell 2011-08-17 12:34:06 Re: Type casting text to Numeric - Query Error
Previous Message Vikram A 2011-08-17 12:17:23 Type casting text to Numeric - Query Error