4

I'm trying to clean out excessive trailing zeros, I used the following query...

UPDATE _table_ SET _column_=trim(trailing '00' FROM '_column_');

...and I received the following error:

ERROR: column "_column_" is of expression is of type text.

I've played around with the quotes since that usually is what it barrels down to for text versus numeric though without any luck.

The CREATE TABLE syntax:

CREATE TABLE _table_ (
 id bigint NOT NULL,
 x bigint,
 y bigint,
 _column_ numeric
);
John
  • 1
  • 13
  • 98
  • 177
  • Please show us the `create table` statement for `_table_` –  Jan 14 '15 at 20:56
  • Aside: Unconditional `UPDATE` is often needlessly expensive. Add a `WHERE` clause to only update rows that actually change. Details: http://stackoverflow.com/questions/54418/how-do-i-or-can-i-select-distinct-on-multiple-columns/12632129#12632129 – Erwin Brandstetter Jan 14 '15 at 21:26

5 Answers5

8

You can cast the arguments from and the result back to numeric:

UPDATE _table_ SET _column_=trim(trailing '00' FROM _column_::text)::numeric;

Also note that you don't quote column names with single quotes as you did.

harmic
  • 28,606
  • 5
  • 67
  • 91
  • 3
    This happens to me a lot - people beating me by 20 seconds or so. :) Upvoting, obviously. – rchang Jan 14 '15 at 21:15
  • 1
    @rchang: harmic also explains the primary cause of the error: invalid quoting. – Erwin Brandstetter Jan 14 '15 at 21:23
  • @ErwinBrandstetter True enough. I was just now thinking that without the explicit cast of the `trim` result back to `numeric` there may have been another error, but I got past my brain fart and realized the implicit conversion would work as long as the resulting string looks reasonably like a number. – rchang Jan 14 '15 at 21:28
  • Another good question (though not necessary for me right now) is how to keep everything to the hundredth value (keeping 1.00 when the value is 1.00 to begin with). Thanks! – John Jan 14 '15 at 21:30
  • 1
    @John: Don't put new questions into comments, please. Ask a new question. Basically: `round(_column_, 2)`. – Erwin Brandstetter Jan 14 '15 at 22:11
  • 3
    Careful with this answer. It will convert `100` into `1`! – Nico Burns Mar 30 '20 at 14:38
  • As @NicoBurns pointed out, this doesn't just remove trailing zeros from after the decimal point. Which is not ideal in a lot of scenarios! – Jon Betts Jan 26 '21 at 19:13
7

Postgres version 13 now comes with the trim_scale() function:

UPDATE _table_ SET _column_ = trim_scale(_column_);
mohammedkhan
  • 953
  • 6
  • 14
2

trim takes string parameters, so _column_ has to be cast to a string (varchar for example). Then, the result of trim has to be cast back to numeric.

UPDATE _table_ SET _column_=trim(trailing '00' FROM _column_::varchar)::numeric;
rchang
  • 5,150
  • 1
  • 15
  • 25
2

Another (arguably more consistent) way to clean out the trailing zeroes from a NUMERIC field would be to use something like the following:

UPDATE _table_ SET _column_ = CAST(to_char(_column_, 'FM999999999990.999999') AS NUMERIC);

Note that you would have to modify the FM pattern to match the maximum expected precision and scale of your _column_ field. For more details on the FM pattern modifier and/or the to_char(..) function see the PostgreSQL docs here and here.

Edit: Also, see the following post on the gnumed-devel mailing list for a longer and more thorough explanation on this approach.

Priidu Neemre
  • 2,813
  • 2
  • 39
  • 40
  • 1
    stumbled across this question when trying to remove trailing zeros from after the decimal point (ie 123.1200 -> 123.12) and this answer works perfectly for me (whilst not effecting numbers such as 1200) - thanks! – Alex Hinton Nov 26 '17 at 21:21
0

Be careful with all the answers here. Although this looks like a simple problem, it's not.

If you have pg 13 or higher, you should use trim_scale (there is an answer about that already). If not, here is my "Polyfill":

DO $x$
BEGIN
IF count(*)=0 FROM pg_proc where proname='trim_scale' THEN
CREATE FUNCTION trim_scale(numeric) RETURNS numeric AS $$
SELECT CASE WHEN trim($1::text, '0') = '.' THEN 0 WHEN trim($1::text, '0')::numeric = $1 THEN trim($1::text, '0')::numeric ELSE $1 END $$
LANGUAGE SQL;
END IF;
END;
$x$;

And here is a query for testing the answers:

WITH test as (SELECT unnest(string_to_array('1|2.0|0030.00|4.123456000|300000|0.00','|'))::numeric _column_)
SELECT _column_ original,
trim(trailing '00' FROM _column_::text)::numeric accepted_answer,
CAST(to_char(_column_, 'FM999999999990.999') AS NUMERIC) another_fancy_one,
CASE WHEN trim(_column_::text, '0') = '.' THEN 0 WHEN trim(_column_::text, '0')::numeric = _column_  THEN trim(_column_::text, '0')::numeric ELSE _column_ END my,
trim_scale(_column_) as the_actual_function FROM test;

Well... it looks like, I'm trying to show the flaws of the earlier answers, while just can't come up with other testcases. Maybe you should write more, if you can.

I'm like short syntax instead of fancy sql keywords, so I always go with :: over CAST and function call with comma separated args over constructs like trim(trailing '00' FROM _column_). But it's a personal taste only, you should check your company or team standards (and fight for change them XD)

n3ko
  • 377
  • 3
  • 8
  • 1
    The suggested solution above didn't work for me for the the test case `trim_scale(0.0::numeric)` in Postgres 12 and fails with `ERROR: invalid input syntax for type numeric: "."`. Swapping to use `rtrim` instead of `trim` fixed it for me. – Lukasz Cwik Jun 09 '23 at 00:51
  • Good catch, ty. Fixed with a tiny edit. (Although replace all `trim` to `rtrim` also would do) As i used to say to my students: it was intentional. (it wasn't) – n3ko Jun 20 '23 at 20:07