Misrepresentation of table values after rounding #166

Closed
darecstowell opened this Issue Jan 22, 2016 · 8 comments

Comments

Projects
None yet
3 participants
@darecstowell

I noticed that Postico was showing the wrong representation of values for random cells after I used the Postgres round(v numeric, s int) function on a whole column.
Here's the specific query - *update schema.table set column = round(cast(column as numeric) 100, 2)
2016-01-22t15-18-43
^ Incorrect representation of random value in Postico. (bottom right)^
2016-01-22t15-40-10
^ Same value in psql. ^
Again, this happened for multiple values throughout the table after rounding, this is just one example.

At first, I thought I was rounding wrong, but myself and a few others checked multiple values and confirmed they were rounded correctly.
Just thought I would share. :)

@darecstowell

This comment has been minimized.

Show comment
Hide comment
@darecstowell

darecstowell Jan 22, 2016

Also, I have ran basic troubleshooting, and continue to see the misrepresentation.

Also, I have ran basic troubleshooting, and continue to see the misrepresentation.

@darecstowell darecstowell changed the title from Misrepresentation of table values after rounding. to Misrepresentation of table values after rounding Jan 25, 2016

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Jan 25, 2016

Owner

Could you check the type of the column? If the type is float8 (double precision), Postico shows 16 significant digits, but doubles only have 15.9 digits of precision, so sometimes odd things like that happen. If you need precise decimal numbers, I recommend working with the NUMERIC type.

Consider the following query:

select 0.07::float4, 0.07::float8, 0.07::numeric

This shows the following result:
screen shot 2016-01-26 at 00 39 27

Postico could just round to 15 digits when displaying numbers, then that wouldn't happen. Since a bunch of people have complained about this, I'll see what I can do.

Owner

jakob commented Jan 25, 2016

Could you check the type of the column? If the type is float8 (double precision), Postico shows 16 significant digits, but doubles only have 15.9 digits of precision, so sometimes odd things like that happen. If you need precise decimal numbers, I recommend working with the NUMERIC type.

Consider the following query:

select 0.07::float4, 0.07::float8, 0.07::numeric

This shows the following result:
screen shot 2016-01-26 at 00 39 27

Postico could just round to 15 digits when displaying numbers, then that wouldn't happen. Since a bunch of people have complained about this, I'll see what I can do.

@darecstowell

This comment has been minimized.

Show comment
Hide comment
@darecstowell

darecstowell Jan 26, 2016

Thanks for the response.
Yup, they are all double precision, which made sense before the rounding.
It's a great app. A good amount of people at my office have come over from pgAdmin. :)
Look forward to your advancements and wish you the best!

Thanks for the response.
Yup, they are all double precision, which made sense before the rounding.
It's a great app. A good amount of people at my office have come over from pgAdmin. :)
Look forward to your advancements and wish you the best!

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Feb 8, 2016

Owner

I fixed this in the latest build. Postico now always uses the shortest possible decimal representation that has the same underlying floating point representation.

You can download the build here:
https://eggerapps-downloads.s3-eu-west-1.amazonaws.com/postico-1316.zip

Owner

jakob commented Feb 8, 2016

I fixed this in the latest build. Postico now always uses the shortest possible decimal representation that has the same underlying floating point representation.

You can download the build here:
https://eggerapps-downloads.s3-eu-west-1.amazonaws.com/postico-1316.zip

@jakob jakob added the done label Feb 8, 2016

@qwesda

This comment has been minimized.

Show comment
Hide comment
@qwesda

qwesda Feb 8, 2016

Sadly this fix messes up numeric columns:

SELECT 3.51::numeric;

output: 3.5100000000000002

I have several tables with numeric columns that exhibit this behaviour.

qwesda commented Feb 8, 2016

Sadly this fix messes up numeric columns:

SELECT 3.51::numeric;

output: 3.5100000000000002

I have several tables with numeric columns that exhibit this behaviour.

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Feb 9, 2016

Owner

@qwesda Thanks a lot for catching this.

Numeric/decimal formatting works again in the following build:
https://eggerapps-downloads.s3-eu-west-1.amazonaws.com/postico-1317.zip

Owner

jakob commented Feb 9, 2016

@qwesda Thanks a lot for catching this.

Numeric/decimal formatting works again in the following build:
https://eggerapps-downloads.s3-eu-west-1.amazonaws.com/postico-1317.zip

@qwesda

This comment has been minimized.

Show comment
Hide comment
@qwesda

qwesda Feb 9, 2016

Seems to work ok now

qwesda commented Feb 9, 2016

Seems to work ok now

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Feb 25, 2016

Owner

This issue is now resolved in Postico 1.0.4
Download here: https://eggerapps.at/postico/

Owner

jakob commented Feb 25, 2016

This issue is now resolved in Postico 1.0.4
Download here: https://eggerapps.at/postico/

@jakob jakob closed this Feb 25, 2016

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment