CODEWITHSUNDEEP
postgresql
John
John

Reputation: 13739

PostgreSQL Trim excessive trailing zeroes: type numeric but expression is of type text

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
);

Upvotes: 5

Views: 11668

Answers (5)

n3ko
n3ko

Reputation: 415

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)

Upvotes: 0

mohammedkhan
mohammedkhan

Reputation: 975

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

UPDATE _table_ SET _column_ = trim_scale(_column_);

Upvotes: 7

Priidu Neemre
Priidu Neemre

Reputation: 3062

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.

Upvotes: 2

rchang
rchang

Reputation: 5236

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;

Upvotes: 1

harmic
harmic

Reputation: 30587

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.

Upvotes: 8

Related Questions