Re: Type casting text to Numeric - Query Error - Mailing list pgsql-general

From Vikram A
Subject Re: Type casting text to Numeric - Query Error
Date
Msg-id 1313639443.81544.YahooMailNeo@web137409.mail.in.yahoo.com
Whole thread Raw
In response to Re: Type casting text to Numeric - Query Error  (Pavel Stehule <pavel.stehule@gmail.com>)
List pgsql-general
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@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


pgsql-general by date:

Previous
From: Fujii Masao
Date:
Subject: Re: streaming replication: one problem & several questions
Next
From: Sim Zacks
Date:
Subject: Re: Using Postgresql as application server