1

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?

Anton Poznyakovskiy
  • 2,109
  • 1
  • 20
  • 38

1 Answers1

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).

The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • 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? – Anton Poznyakovskiy Dec 17 '18 at 21:10
  • 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: https://stackoverflow.com/questions/46334478/convert-bytea-to-double-precision-in-postgresql – The Impaler Dec 17 '18 at 21:26