1

Context

I run into (some good old) troubles when trying to update a PostgreSQL (10.12) table called nodes containing nodes with some few attributes from OSM + some few others.
I only want to update the OSM attribute (lat, lon, osmid, highway, geom) but not the few others I also have on the table nodes.

In order to update this table nodes without breaking it, I took a security margin and created an other clean table called osmnodes where I first fetch the OSM basic data I need (see above) using ogr2ogr (...do some pre-processing...) and, only after that, I upgrade my main table nodes as follows:

INSERT INTO schema.nodes
   (id, lat, lon, osmid, highway, geom) -- I only want to upgrade these basic OSM fields, not the others
OVERRIDING SYSTEM VALUE -- this is mandatory, see the note hereunder
SELECT
     b.fid, b.x, b.y,
     b.osmid::bigint, b.highway, b.geom
FROM osmschema.osmnodes b;

Note that id of table schema.nodes was initially defined as: id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY.

Error 1

This obviously sends me a duplicate key error:

ERROR:  duplicate key value violates unique constraint "nodes_pkey"
DETAIL:  Key (id)=(1) already exists.

So I naturally went for a coupled update such as:

INSERT INTO schema.nodes
   (id, lat, lon, osmid, highway, geom)
OVERRIDING SYSTEM VALUE
SELECT
     b.fid, b.x, b.y,
     b.osmid::bigint, b.highway, b.geom
FROM osmschema.osmnodes b

ON CONFLICT (osmid) DO
UPDATE SET
(lat, lon, highway, geom) -- removed the id here as I want to keep its already present value
=
b.x, b.y, b.highway, b.geom
-- FROM osmschema.osmnodes b -- the line to be freed to get to the next step hereunder
WHERE b.osmid = nodes.osmid; -- update the matching id

Error 2

Then I get:

ERROR:  missing FROM-clause entry for table "b"
LINE 13: (b.x, b.y, b.highway, b.geom)
          ^

Error 3

And, If I add the FROM clause which is asked for (un-comment the line in the previous snippet):

ERROR:  syntax error at or near "FROM"
LINE 14: FROM osmschema.osmnodes b

Question

I'm missing something there (...), I thought I would be simple...
The OVERRIDING SYSTEM VALUE seems not to work after it's been called.
What's wrong with my statement?

1 Answer 1

2

The issue is with the id field, not osmid, so the workaround won't work.

The second issue is on the upsert. You need to refer to EXCLUDED. to get the provided values, there is no need to re-query the tables.

ON CONFLICT (id) DO
UPDATE SET
 lat=EXCLUDED.lat, 
 lon=EXCLUDED.lon, 
 highway=EXCLUDED.highway, 
 geom=EXCLUDED.geom;

At last, note that you have swapped X/Y and lat/long (X should be longitude)

4
  • Thanks². Didn't know about the EXCLUDED keyword! But a small question here; instead of writing many equals sign to assign each value individually, would it be possible to write let's say, a single "block assignment" such as (val1, val2, ...valN) = (EXCLUDED.val1, EXCLUDED.val2, ... EXCLUDED.valN) ? It seems yes from what I've tried, maybe you can add both solutions; I always find my self more comfortable with the second while setting lots of values that I copy-paste from any previous query for example. May 7, 2020 at 18:52
  • 1
    it works for update so it is likely ok here too... but I haven't tried it (for lots of fields, a spreadsheet + code or an sql query will write them for me :)
    – JGH
    May 7, 2020 at 19:01
  • Are by chance osmid unique? Because in my case, I'm not sure the id, which was set automatically, will always represent the same feature between the existing one in table nodes and the one coming from the osmnodes table. Indeed, little changes on the way I query the data may lead to a feature tagged id=1 which is totally different than the feature id=1 in the existing table I'd like to update. Hence, I'd like to rely on the osmid (even if not perfectly reliable). I guess I have to make this field unique as well. May 7, 2020 at 19:28
  • I hate this field (osm_id) because it looks unique (for points at least) and constant, but it is neither. The osm_id can also be recycled to a completely unrelated object. That being said, you can also rely on other attributes to decide if the feature is the same or not (but that's a completely different question)
    – JGH
    May 7, 2020 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.