Skip to content
Advertisement

PostgreSQL query update returns UPDATE 0

I am having trouble with a PostgreSQL query where my update condition contains more prerequisites.

I attached 2 images to describe my problem.

I want to update the highlighted row

UPDATE locations
SET lat = 48.20325265836398, long = 16.34969524923078
WHERE prof_id = 58 AND lat = 47.5327567434237 AND long = 21.6292262077332

But the response is:

UPDATE 0 Query returned successfully in 58 msec.

Advertisement

Answer

Your problem might be somewhere else (see this db<>fiddle)

CREATE TEMPORARY TABLE locations (
id INT, prof_id INT, lat NUMERIC, long NUMERIC);

INSERT INTO locations VALUES 
(57,58,47.5327567434237,21.6292262077332);

UPDATE locations SET lat = 48.20325265836398, long = 16.34969524923078 
WHERE prof_id = 58 AND lat = 47.5327567434237 AND long = 21.6292262077332;

SELECT * FROM  locations;

 id | prof_id |        lat        |       long        
----+---------+-------------------+-------------------
 57 |      58 | 48.20325265836398 | 16.34969524923078
(1 Zeile)

A few thoughts on your data:

  • Reduce the level of precision of your coordinates. With 13 decimals you’re in the realm of microns, which isn’t very useful in geo applications. As you can see yourself, this level of precision it makes = queries quite challenging.
  • Consider using PostGIS to store your coordinates. It has over 1.3k kickass functions that make dealing with geospatial data very easy and efficient. It might seem complex in the beginning but believe me, it will definitely pay off on the long run!
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement