How to prevent PostgreSQL from rounding the output of double precision values?

Question

I have a table of locations with associated data combined from multiple providers. I had the provider id, the latitude and the longitude (both coordinates are double precision) as primary keys. But now, I would like to generate an id of type varchar(50) from the provider, the latitude and the longitude (both coordinates are double-precision values):

UPDATE mytable 
   SET id = CONCAT(provider, '-', latitude, '-', longitude)

But when I try to set the new column as primary key

ALTER TABLE mytable ADD PRIMARY KEY (id)

I get the error

Key (id)=(2-53.69706-12.71236) is duplicated.

Turns out that there are indeed two entries with id='2-53.69706-12.71236', but only one of them has longitude 12.71236. The other one has longitude 12.71235999999999. And while I can select them by these different longitudes:

SELECT
    longitude,
    to_char(longitude, '999D9999999999999999') AS longitude_to_char,
    data->>'ID' AS id_within_provider
FROM mytable
WHERE provider=2 AND latitude=53.69706 AND longitude=12.71236

longitude | longitude_to_char  | id_within_provider
 12.71236 |   12.7123600000000 | 42266

SELECT
    longitude,
    to_char(longitude, '999D9999999999999999') AS longitude_to_char,
    data->>'ID' AS id_within_provider
FROM mytable
WHERE provider=2 AND latitude=53.69706 AND longitude=12.71235999999999

longitude | longitude_to_char  | id_within_provider 
 12.71236 |   12.7123600000000 | 84742

the output longitude stays the same even if I try to convert it to char with an explicit precision. Funny enough, to_char(12.71235999999999, '999D9999999999999999') yields the original number while to_char(longitude, '999D9999999999999999') always yields the rounded number.

So I've got two different double precision values that I have no way to output in a different way, how they should be. What can I do? Am I doing something wrong?

What are you defining an id like this? Just use a serial column. — Gordon Linoff
– Gordon Linoff, Commented Dec 17, 2018 at 19:44

The Impaler · Accepted Answer · 2018-12-17 20:05:21Z

1

Trying to format double precision values into unique varchar values is nearly impossible. In short, you are fighting a losing battle.

The clear solution here is to use a composite primary key:

ALTER TABLE mytable ADD PRIMARY KEY (provider, latitude, longitude)

Alternatively, you can use a SERIAL column as the primary key, and add a unique constraint to these columns (though I don't see why).

answered Dec 17, 2018 at 20:05

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anton Poznyakovskiy Over a year ago

I want to prevent a composite primary key because I want to create another table where the columns would be the ID's of the locations (it should be a distance matrix). Looks like a SERIAL column is a good fit for this, will wait if a better solution comes up. What I don't get is: A double precision value has some sort of bitwise representation. So if these representations are different for X and Y then I should be able to print them out differently, shouldn't I?

The Impaler Over a year ago

It seems that there's no function immediately available to convert from double precision to byte array or similar. However, some people have added a simple extension to the database to perform the conversion that may be what you need. Not for the faint of heart, but take a look at: stackoverflow.com/questions/46334478/…

Collectives™ on Stack Overflow

How to prevent PostgreSQL from rounding the output of double precision values?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related