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
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;
id of table
schema.nodes was initially defined as:
id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY.
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
Then I get:
ERROR: missing FROM-clause entry for table "b" LINE 13: (b.x, b.y, b.highway, b.geom) ^
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
I'm missing something there (...), I thought I would be simple...
OVERRIDING SYSTEM VALUE seems not to work after it's been called.
What's wrong with my statement?