CODEWITHSUNDEEP
postgresqlnumber-formatting
Robin
Robin

Reputation: 83

Display float column with leading sign

I am using PHP with PostgreSQL. I have the following query:

SELECT ra, de, concat(ra, de) AS com, count(*) OVER() AS total_rows
FROM mdust
LIMIT :pagesize OFFSET :starts

The columns ra and de are floats where de can be positive or negative, however, the de does not return the + associated with the float. It does however return the - negative sign. What I want is for the de column within concat(ra, de) to return back the positive or negative sign.

I was looking at this documentation for PostgreSQL which provides to_char(1, 'S9') which is exactly what I want but it only works for integers. I was unable to find such a function for floats.

Upvotes: 0

Views: 151

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658472

to_char() works for float as well. You just have to define desired output format. The simple pattern S9 would truncate fractional digits and fail for numbers > 9.

test=> SELECT to_char(float '0.123'  , 'FMS9999990.099999')
test->      , to_char(float '123'    , 'FMS9999990.099999')
test->      , to_char(float '123.123', 'FMS9999990.099999');
 to_char | to_char | to_char  
---------+---------+----------
 +0.123  | +123.0  | +123.123
(1 row)

The added FM modifier stands for "fill mode" and suppresses insignificant trailing zeroes (unless forced by a 0 symbol instead of 9) and padding blanks.

Add as many 9s before and after the period as you want to allow as many digits.

You can tailor desired output format pretty much any way you want. Details in the manual here.

Aside: There are more efficient solutions for paging than LIMIT :pagesize OFFSET :starts:

Upvotes: 2

Related Questions