Skip site navigation (1) Skip section navigation (2)

Re: Converting between varchar and float when updating

From: Thomas Larsen Wessel <mrvelle(at)gmail(dot)com>
To: Vibhor Kumar <vibhor(dot)kumar(at)enterprisedb(dot)com>
Cc: Dmitriy Igrishin <dmitigr(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Converting between varchar and float when updating
Date: 2011-04-28 13:26:33
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
Thanks a lot :)

Both of the following work

UPDATE foo SET bar = (bar::float * 2);
removes trailing zeros on the decimal side, if no decimals dont show any "."

UPDATE foo SET bar = (bar::numeric * 2);
keeps decimals, i.e. 2.000 * 2 -> 4.000

That leads me to two additional questions:

1) Can I specify how many decimals I want to be stored back from the result?
E.g. 2 / 3 = 0.66666666 but I want to just save 0.66.

2) Can I make a criteria that it should only update on the strings that can
be converted. Maybe smth. like:
UPDATE foo SET bar = (bar::numeric * 2) WHERE bar::is_numeric;


P.S.: Dmitriy asked why I save these values in VarChar. Well, I agree, that
they should be numeric, but I did not design the schema which is btw 10
years old.

On Thu, Apr 28, 2011 at 12:18 PM, Vibhor Kumar <
vibhor(dot)kumar(at)enterprisedb(dot)com> wrote:

> On Apr 28, 2011, at 3:41 PM, Dmitriy Igrishin wrote:
> > Only one point, Vibhor. I believe that varchar data type was chosen for
> > exact storage of numeric values. According to chapter 8.1.3 of the doc.
> > for this case the usage of numeric is preferred over floating data types.
> Ah! Got it. This I have missed.
> Thanks & Regards,
> Vibhor Kumar
> EnterpriseDB Corporation
> The Enterprise PostgreSQL Company
> vibhor(dot)kumar(at)enterprisedb(dot)com
> Blog:

In response to


pgsql-general by date

Next:From: Karsten HilbertDate: 2011-04-28 13:30:55
Subject: Re: plpython module import errors
Previous:From: David BorehamDate: 2011-04-28 13:21:41
Subject: Re: SSDs with Postgresql?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group