CONCAT function adding extra characters

Hi

út 15. 6. 2021 v 20:56 odesílatel AI Rumman <rummandba@gmail.com> napsal:

I am using Postgresql 10 and seeing a strange behavior in CONCAT function
when I am concatenating double precision and int with a separator.

select concat('41.1'::double precision,':', 20);

Result:
41.1000000000000014:20

Value 41.1 which double precision converts to 41.1000000014.

Is that expected?

this is strange

postgres=# select concat('41.1'::double precision,':', 20);
┌─────────┐
│ concat │
╞═════════╡
│ 41.1:20 │
└─────────┘
(1 row)

postgres=# select version();
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ version
│
╞═══════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ PostgreSQL 10.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.1.1
20210428 (Red Hat 11.1.1-1), 64-bit │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(1 row)

Regards

Pavel

On 6/15/21 11:55 AM, AI Rumman wrote:

I am using Postgresql 10 and seeing a strange behavior in CONCAT
function when I am concatenating double precision and int with a separator.

select concat('41.1'::double precision,':', 20);
Result:
41.1000000000000014:20

Value 41.1 which double precision converts to 41.1000000014.

Is that expected?

No.

What OS and version of same?

How was Postgres installed?

Thanks.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 6/15/21 11:55 AM, AI Rumman wrote:

I am using Postgresql 10 and seeing a strange behavior in CONCAT
function when I am concatenating double precision and int with a separator.

select concat('41.1'::double precision,':', 20);
Result:
41.1000000000000014:20

Value 41.1 which double precision converts to 41.1000000014.

Is that expected?

Aah, too quick on the trigger.

Also:

Is there a home brewed version of CONCAT() in the search_path?

Thanks.

--
Adrian Klaver
adrian.klaver@aklaver.com

AI Rumman <rummandba@gmail.com> writes:

I am using Postgresql 10 and seeing a strange behavior in CONCAT function
when I am concatenating double precision and int with a separator.

select concat('41.1'::double precision,':', 20);

Result:
41.1000000000000014:20

What have you got extra_float_digits set to?

regards, tom lane

�t 15. 6. 2021 v 20:56 odes�latel AI Rumman <rummandba@gmail.com> napsal:

I am using Postgresql 10 and seeing a strange behavior in CONCAT function
when I am concatenating double precision and int with a separator.

select concat('41.1'::double precision,':', 20);

Result:
41.1000000000000014:20

Value 41.1 which double precision converts to 41.1000000014.

Is that expected?

Hi

0.1 cannot be represented exactly in binary so that does not look out of
line. There are also some config options for extra digits and what not
that may affect the result of a cast.

Regards,
Ken

út 15. 6. 2021 v 21:07 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

AI Rumman <rummandba@gmail.com> writes:

I am using Postgresql 10 and seeing a strange behavior in CONCAT function
when I am concatenating double precision and int with a separator.

select concat('41.1'::double precision,':', 20);

Result:
41.1000000000000014:20

What have you got extra_float_digits set to?

postgres=# set extra_float_digits to 3;
SET
postgres=# select concat('41.1'::double precision,':', 20);
┌────────────────────────┐
│ concat │
╞════════════════════════╡
│ 41.1000000000000014:20 │
└────────────────────────┘
(1 row)

Pavel

On 6/15/21 1:55 PM, AI Rumman wrote:

I am using Postgresql 10 and seeing a strange behavior in CONCAT function
when I am concatenating double precision and int with a separator.

select concat('41.1'::double precision,':', 20);
Result:
41.1000000000000014:20

Value 41.1 which double precision converts to 41.1000000014.

Is that expected?

Because of the well-known difficulty in precisely converting floating point
to decimal, in cases like this, I always cast to NUMERIC of the desired
precision.  It's the COBOL programmer in me...

--
Angular momentum makes the world go 'round.

I saw that problem when I was running the query from DBeaver.
Got my answer.

Thanks & Regards.

On Tue, Jun 15, 2021 at 12:18 PM Pavel Stehule <pavel.stehule@gmail.com>
wrote: