Function to interpret numeric literal with shifted decimal point

Question

I'm looking for "interpreting string as decimal" function in PostgreSQL without inserting the decimal manually. The result should be the same as below but without string manipulation:

SELECT COALESCE(NULLIF(left('123456789', -3), ''), '0') || '.' || right('123456789', 3);
-- Should be 123456.789

SELECT COALESCE(NULLIF(left('123', -3), ''), '0') || '.' || right('123', 3);
-- Should be 0.123

I'm also accepting simpler / faster solutions if there is no built-in way in PostgreSQL.

I skipped the cast to decimal in the example.

Do you mean the `pow` function? `select '123456789' * pow(10,-3), '123' * pow(10,-3)` returns `123456.789` and `0.123` — EdmCoff, Aug 04 '22 at 19:32
`SELECT '123456789'::float/1000, '123'::float/1000;` (When using decimal, in stead of float, you will get more decimals, but with there values those extra decimals will all be '0'., and the COALESCE is only needed when you need support for the null-value. — Luuk, Aug 04 '22 at 19:34
Just a thought on avoiding math manipulation and just a cleaner insert the decimal point method: `reverse(overlay(reverse(val) placing '.' from length+1 for 0))` https://dbfiddle.uk/?rdbms=postgres_10&fiddle=f32786b6ef5bd5ba87cc5f4b43f4bb49 — shawnt00, Aug 04 '22 at 20:47
Your version of Postgres? Can input be '0' / null / negative / ... Possible range of fractional digits? Output is type `numeric` (= `decimal`)? Be precise. — Erwin Brandstetter, Aug 04 '22 at 21:55
@ErwinBrandstetter 14.2, non-null, big numbers probably up to 10^24 with up to 32 fractional digits, should be numeric (sorry), thanks for your answer but I think the comments already answered my question, answer was at hindsight but was convinced I would lose precision that way — cup_of_chino, Aug 06 '22 at 10:35

Erwin Brandstetter · Answer 1 · 2022-08-04T23:41:43.933

Postgres 13 added trim_scale(), which is somewhat useful. The manual:

trim_scale ( numeric ) → numeric

Reduces the value's scale (number of fractional decimal digits) by removing trailing zeroes

trim_scale(8.4100) → 8.41

Apply that to the numeric value after cast & multiplication:

SELECT trim_scale('123'::numeric / (10^3)::int);

Simpler:

SELECT trim_scale('123'::numeric / 1000);

General form:

SELECT trim_scale(string::numeric / (10^dec_offset)::int);

db<>fiddle here

^ is the "exponentiation" operator - returning double precision, hence the added cast to not introduce floating point inaccuracy.

Works for any positive or negative numeric literal, including 0, -0, and numbers with fractional digits.

To also allow negative offsets (-3 instead of 3, shifting decimal point to the right) use:

SELECT trim_scale(string::numeric / (10^dec_offset)::numeric);

But the simpler version with integer above is around twice as fast.

Remove trailing zeroes from numeric

Function to interpret numeric literal with shifted decimal point

1 Answers1