PostgreSQL query update returns UPDATE 0

Question

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.

Welcome to SO. Please post query and data as text, not images. — Jim Jones
– Jim Jones, Commented Feb 28, 2021 at 11:18
Unrelated: why are you storing coordinates with this level of precision? You're there in the realm of microscopy — Jim Jones
– Jim Jones, Commented Feb 28, 2021 at 11:20
Thank you, I added the code. The reason of this precise coordinate storing is that we will soon put a function in our app where you can see the services nearby on the map. — Tamás Tényi
– Tamás Tényi, Commented Feb 28, 2021 at 11:23
Fair enough, but I doubt that your "nearby" function would work with microns. Wouldn't metres/feet suffice? — Jim Jones
– Jim Jones, Commented Feb 28, 2021 at 11:26
Yes, you may be right. I didn't know how precise is this. Thanks for the advice I will cut down by a few precision points — Tamás Tényi
– Tamás Tényi, Commented Feb 28, 2021 at 11:30

Jim Jones · Accepted Answer · 2021-02-28 11:46:47Z

1

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!

edited Feb 28, 2021 at 11:46

answered Feb 28, 2021 at 11:34

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

4 Comments

Tamás Tényi Over a year ago

Thank you, Jim. I am storing my lat and long values in double precision. You are right I need to lower the precision level, I don't need that precise coordinates. Now I changed it to numeric type and it worked. Thank you very much :))

Jim Jones Over a year ago

@TamásTényi I'm glad it helped! Really consider using geography or geometry to store your coordinates. PostGIS just rocks ;)

Tamás Tényi Over a year ago

One more question @Jim Jones. How many digits should I need after the decimal point to make it 10 metres precise?

Jim Jones Over a year ago

@TamásTényi according to this post 4-5 digits would suffice ;) gis.stackexchange.com/a/8674

jordanvrtanoski · Accepted Answer · 2021-02-28 11:31:34Z

1

Never assume that the decimal places you are seeing are the exact number that is stored in the table.

In your case, only id or prof_id is enough to update the row (as I can see from the attachments), so, no need of the lat and long, however in cases where you are forced to use the decimal numbers in comparison during update (and I will argue if this is the case you have bad design of the database tables), always round them to controllable decimal point as follows:

UPDATE locations SET 
       lat = 48.20325265836398, 
       long = 16.34969524923078 
WHERE  prof_id = 58 
       AND ROUND(lat,13) = 47.5327567434237 
       AND ROUND(long,13) = 21.6292262077332

answered Feb 28, 2021 at 11:31

jordanvrtanoski

5,6271 gold badge25 silver badges31 bronze badges

1 Comment

Tamás Tényi Over a year ago

As Jim mentioned before you are right, I will lower the precision level. The problem was I stored it in double precision now I changed to numeric and it works. Thank you for the advice.

Collectives™ on Stack Overflow

PostgreSQL query update returns UPDATE 0

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related