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?