Update PostgreSQL table; insert data from subquery, on conflict do update duplicate id

Question

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?

Answer 1

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)