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!