Thread: CONCAT function adding extra characters
I am using Postgresql 10 and seeing a strange behavior in CONCAT function when I am concatenating double precision and int with a separator.
Value 41.1 which double precision converts to 41.1000000014.
Is that expected?
Thanks.
select concat('41.1'::double precision,':', 20);
Result:
41.1000000000000014:20
Value 41.1 which double precision converts to 41.1000000014.
Is that expected?
Thanks.
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)
┌─────────┐
│ 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
Thanks.
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)
SET
postgres=# select concat('41.1'::double precision,':', 20);
┌────────────────────────┐
│ concat │
╞════════════════════════╡
│ 41.1000000000000014:20 │
└────────────────────────┘
(1 row)
Pavel
regards, tom lane
On 6/15/21 1:55 PM, AI Rumman wrote:
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...
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.
Angular momentum makes the world go 'round.
I saw that problem when I was running the query from DBeaver.
Got my answer.
Got my answer.
Thanks & Regards.
On Tue, Jun 15, 2021 at 12:18 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
ú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)Pavelregards, tom lane