Обсуждение: Format an Update with calculation

Поиск

Список

Период

Сортировка

Format an Update with calculation

От

Bret Stern

Дата:

18 декабря 2018 г., 10:14:57

My statement below updates the pricing no problem, but I want it to be
formatted with 2 dec points eg (43.23).

Started playing with to_numeric but can't figure it out. Lots of examples with to_char in the
manual, but still searching for answer.

Can it be done?

I want suggested_retail_price to be formatted to 2 decimal points

UPDATE im_ci_item_transfer
SET suggested_retail_price=(suggested_retail_price + (suggested_retail_price * .13))
WHERE item_code='0025881P2';

Feeling lazy, sorry guys

Re: Format an Update with calculation

От

Pavel Stehule

Дата:

18 декабря 2018 г., 10:18:13

út 18. 12. 2018 v 8:15 odesílatel Bret Stern <bret_stern@machinemanagement.com> napsal:

My statement below updates the pricing no problem, but I want it to be
formatted with 2 dec points eg (43.23).

Started playing with to_numeric but can't figure it out. Lots of examples with to_char in the
manual, but still searching for answer.

Can it be done?

postgres=# select random();
┌───────────────────┐
│ random │
╞═══════════════════╡
│ 0.261391982901841 │
└───────────────────┘
(1 row)

postgres=# select random()::numeric(7,2);
┌────────┐
│ random │
╞════════╡
│ 0.67 │
└────────┘
(1 row)

Regards

Pavel

I want suggested_retail_price to be formatted to 2 decimal points

UPDATE im_ci_item_transfer
SET suggested_retail_price=(suggested_retail_price + (suggested_retail_price * .13))
WHERE item_code='0025881P2';

Feeling lazy, sorry guys

Re: Format an Update with calculation

От

Andrew Gierth

Дата:

18 декабря 2018 г., 16:49:55

>>>>> "Bret" == Bret Stern <bret_stern@machinemanagement.com> writes:

 Bret> My statement below updates the pricing no problem, but I want it
 Bret> to be formatted with 2 dec points eg (43.23).

UPDATE ...
   SET suggested_retail_price = round(suggested_retail_price*1.13, 2)
 WHERE ...

assuming suggested_retail_price is already of type "numeric".

You could also have given the columm a type of numeric(18,2) or similar,
which would round all assigned values to 2 places.

-- 
Andrew (irc:RhodiumToad)

Re: Format an Update with calculation

От

Adrian Klaver

Дата:

18 декабря 2018 г., 16:51:16

On 12/17/18 11:14 PM, Bret Stern wrote:
> My statement below updates the pricing no problem, but I want it to be
> formatted with 2 dec points eg (43.23).
> 
> Started playing with to_numeric but can't figure it out. Lots of 
> examples with to_char in the
> manual, but still searching for answer.
> 
> Can it be done?
> 
> I want suggested_retail_price to be formatted to 2 decimal points
> 
> UPDATE im_ci_item_transfer
>     SET suggested_retail_price=(suggested_retail_price + 
> (suggested_retail_price * .13))
> WHERE item_code='0025881P2';
> 
> Feeling lazy, sorry guys

In addition to what Pavel posted:

select round(43.2335, 2);

  round
-------
  43.23




-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Format an Update with calculation

От

Bret Stern

Дата:

18 декабря 2018 г., 18:51:13

Thanks again,
I don't remember ever using a select in an update.
Not sure how to use a select in an update, I'll google around.


On Tue, 2018-12-18 at 08:18 +0100, Pavel Stehule wrote:
> 
> 
> 
> út 18. 12. 2018 v 8:15 odesílatel Bret Stern
> <bret_stern@machinemanagement.com> napsal:
> 
>         My statement below updates the pricing no problem, but I want
>         it to be
>         formatted with 2 dec points eg (43.23).
>         
>         Started playing with to_numeric but can't figure it out. Lots
>         of examples with to_char in the
>         manual, but still searching for answer.
>         
>         Can it be done?
>         
> 
> 
> postgres=# select random();
> ┌───────────────────┐
> │      random       │
> ╞═══════════════════╡
> │ 0.261391982901841 │
> └───────────────────┘
> (1 row)
> 
> postgres=# select random()::numeric(7,2);
> ┌────────┐
> │ random │
> ╞════════╡
> │   0.67 │
> └────────┘
> (1 row)
> 
> 
>  
> Regards
> 
> 
> Pavel
> 
> 
>         
>         I want suggested_retail_price to be formatted to 2 decimal
>         points
>         
>         UPDATE im_ci_item_transfer
>            SET suggested_retail_price=(suggested_retail_price +
>         (suggested_retail_price * .13))
>         WHERE item_code='0025881P2';
>         
>         Feeling lazy, sorry guys

Re: Format an Update with calculation

От

Ron

Дата:

18 декабря 2018 г., 19:40:19

Bret,

That's just an example of how to use Postgres' cast syntax.  You'd write:
UPDATE im_ci_item_transfer
    SET suggested_retail_price=(suggested_retail_price + 
(suggested_retail_price * .13))::numeric(7,2)
WHERE item_code='0025881P2';

I prefer the round() function, though.


On 12/18/2018 09:51 AM, Bret Stern wrote:
> Thanks again,
> I don't remember ever using a select in an update.
> Not sure how to use a select in an update, I'll google around.
>
>
> On Tue, 2018-12-18 at 08:18 +0100, Pavel Stehule wrote:
>>
>>
>> út 18. 12. 2018 v 8:15 odesílatel Bret Stern
>> <bret_stern@machinemanagement.com> napsal:
>>
>>          My statement below updates the pricing no problem, but I want
>>          it to be
>>          formatted with 2 dec points eg (43.23).
>>          
>>          Started playing with to_numeric but can't figure it out. Lots
>>          of examples with to_char in the
>>          manual, but still searching for answer.
>>          
>>          Can it be done?
>>          
>>
>>
>> postgres=# select random();
>> ┌───────────────────┐
>> │      random       │
>> ╞═══════════════════╡
>> │ 0.261391982901841 │
>> └───────────────────┘
>> (1 row)
>>
>> postgres=# select random()::numeric(7,2);
>> ┌────────┐
>> │ random │
>> ╞════════╡
>> │   0.67 │
>> └────────┘
>> (1 row)
>>
>>
>>   
>> Regards
>>
>>
>> Pavel
>>
>>
>>          
>>          I want suggested_retail_price to be formatted to 2 decimal
>>          points
>>          
>>          UPDATE im_ci_item_transfer
>>             SET suggested_retail_price=(suggested_retail_price +
>>          (suggested_retail_price * .13))
>>          WHERE item_code='0025881P2';
>>          
>>          Feeling lazy, sorry guys
>
>

-- 
Angular momentum makes the world go 'round.

Re: Format an Update with calculation

От

Condor

Дата:

19 декабря 2018 г., 11:12:04

On 18-12-2018 15:51, Adrian Klaver wrote:
> On 12/17/18 11:14 PM, Bret Stern wrote:
>> My statement below updates the pricing no problem, but I want it to be
>> formatted with 2 dec points eg (43.23).
>>
>> Started playing with to_numeric but can't figure it out. Lots of
>> examples with to_char in the
>> manual, but still searching for answer.
>>
>> Can it be done?
>>
>> I want suggested_retail_price to be formatted to 2 decimal points
>>
>> UPDATE im_ci_item_transfer
>>     SET suggested_retail_price=(suggested_retail_price +
>> (suggested_retail_price * .13))
>> WHERE item_code='0025881P2';
>>
>> Feeling lazy, sorry guys
>
> In addition to what Pavel posted:
>
> select round(43.2335, 2);
>
>  round
> -------
>  43.23


Beware with round and numeric

select round(43.2375, 2);
  round
-------
  43.24


  select 43.2375::numeric(17, 2);
  numeric
---------
    43.24

Regards,
HS

Re: Format an Update with calculation

От

Ron

Дата:

19 декабря 2018 г., 17:04:39

On 12/19/2018 02:12 AM, Condor wrote:
> On 18-12-2018 15:51, Adrian Klaver wrote:
[snip]
>> In addition to what Pavel posted:
>>
>> select round(43.2335, 2);
>>
>>  round
>> -------
>>  43.23
>
>
> Beware with round and numeric
>
> select round(43.2375, 2);
>  round
> -------
>  43.24
>
>
>  select 43.2375::numeric(17, 2);
>  numeric
> ---------
>    43.24

Beware of what?


-- 
Angular momentum makes the world go 'round.

Re: Format an Update with calculation

От

Ken Tanzer

Дата:

19 декабря 2018 г., 21:04:39

On Tue, Dec 18, 2018 at 5:51 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 12/17/18 11:14 PM, Bret Stern wrote:
> My statement below updates the pricing no problem, but I want it to be
> formatted with 2 dec points eg (43.23).
>
> Started playing with to_numeric but can't figure it out. Lots of
> examples with to_char in the
> manual, but still searching for answer.
>
> Can it be done?
>
> I want suggested_retail_price to be formatted to 2 decimal points
>
> UPDATE im_ci_item_transfer
> SET suggested_retail_price=(suggested_retail_price +
> (suggested_retail_price * .13))
> WHERE item_code='0025881P2';
>
> Feeling lazy, sorry guys

In addition to what Pavel posted:

select round(43.2335, 2);

round
-------
43.23

I think this discussion is missing an important point, which is the difference between how a value is stored (i.e., what ends up as suggested_retail_price in your table), and how it is formatted when you select or use it. Whatever value you select in this update is going to get converted to the column's datatype anyway. You haven't told us what the datatype for s_r_p is, but consider this example:

CREATE TEMP TABLE price (
my_dec_2 DECIMAL(8,2),
my_numeric NUMERIC,
my_numeric_2 NUMERIC (8,2),
my_money MONEY
);
WITH num as (SELECT 4.2375914 AS base)
INSERT INTO price
SELECT base,base,base,base FROM num;

SELECT * FROM price;

 my_dec_2 | my_numeric | my_numeric_2 | my_money 
----------+------------+--------------+----------    4.24 |  4.2375914 |         4.24 |    $4.24

So if you want your prices to be limited to 2 decimal places, just define the columns as such. All the formatting mentioned in this thread is only needed for outputting, selecting on the fly, etc.

Cheers,

Ken

AGENCY Software

A Free Software data system

By and for non-profits

http://agency-software.org/

https://demo.agency-software.org/client

ken.tanzer@agency-software.org

(253) 245-3801

Subscribe to the mailing list to

learn more about AGENCY or

follow the discussion.

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: Format an Update with calculation