Float values to String in Postgresql

Question

I'm using PgSQL 9.1.

SELECT 5.1::text 

is working fine with output '5.1' in PgAdmin3, but with JDBC Prepared statement EXEC SQL, the result always as '5.0999999'.

Of source SELECT to_char(5.1, '9.9') will work, however this is not what I want. I want to get '5' in the case of 5.0 without decimal point, so I just stay on the ::text conversion.

My assumption is there might be some session environment settings affected to this conversion somewhere, but I can't figure out how to find.

I know this is just a silly question... please help.

Suburbia

Answer 1

I think I found a solution. Try to cast into numeric type first...

SELECT (5.1::numeric)::text 

Please add your answer if you have a better solution.

Suburbia

Answer 2

Do the following:

select cast(5.1 as text)

Grolier

Answer 3

In 2024 for Postgres 13.2 it works best as

SELECT (5.1::real)::text

For example, let's take an field test numeric(32,4) not null default 0. Then:

SELECT t.test FROM test_table t
-- 5.1000
SELECT t.test::text FROM test_table t
-- 5.1000
SELECT t.test::numeric::text FROM test_table t
-- 5.1000
SELECT t.test::real::text FROM test_table t
-- 5.1

Helgahelge