This is a static archive of our old OpenStreetMap Help Site. Please post any new questions and answers at community.osm.org.

Cannot append to table planet_osm_point (SRID 3857) using selected SRID 900913, and “INSERT has more expressions than target columns”

My openstreetmap database have import the asia.osm.pbf, and the gis database have import the planet-lastest.osm.pbf, when i make an edit on the openstreetmap db by iD editor, and use osmosis command to produce a changes.osc.gz file, then i use the "osm2pgsql --append --slim -H 192.168.1.162 -P 5432 -d gis -U postgres -C 160 --number-processes 3 changes.osc.gz -e15 -o expire.list" command to import the changes.osc.gz into gis db, some error occurs, i don't know how to solve it. And i didn't find a solution on the internet, so i ask a question here, hope someone to help me.

alt text

This error disappears when i add a projection parameter "-E 3857", but other error occurs, the detail error message is:

osm2pgsql SVN version 0.82.0 (64bit id space)
Using projection SRS 3857 (EPSG:3857)
....
failed: ERROR:  INSERT has more expressions than target columns
LINE 1: ...oolean) AS INSERT INTO planet_osm_ways VALUES ($1,$2,$3,$4);
                                                               ^

editing osm2pgsql

asked 30 Dec '16, 13:57

yuyy
236●22●24●31
accept rate: 20%

edited 02 Jan '17, 10:27

Frederik Ramm ♦
82.5k●92●720●1273

osm2pgsql's default projection is 900913, but your tables from first (and/or previous) import is in 3087. The simplest way round this is to add a projection parameter "-E 3857". (although 3857 & 900913 are for most purposes identical, PostGIS does not know this)

(30 Dec '16, 15:35) SK53 ♦

One Answer:

It appears that you have downgraded your osm2pgsql version from 0.92 or later to 0.82 after doing the first import. This would explain your initial problem (since 0.92 started using EPSG:3857 as the default, while earlier versions were using EPSG:900913), as well as your follow-on problem (because osm2pgsql 0.82 was using an additional column called "pending" on the database tables that was later removed).

While you found a valid workaround for the projection issue, a workaround for the missing columns would be more cumbersome. (See https://github.com/openstreetmap/osm2pgsql/blob/master/docs/migrations.md for information on migrating osm2pgsql-imported data between versions, but note that 0.82 is so old that it might already warrant the attribute "ancient".)

You should be able to solve your problem by using the same osm2pgsql version for the initial import and the subsequent updates.

(Hat tip to lonvia and pnorman who told me this on IRC.)

answered 02 Jan '17, 10:32

Frederik Ramm ♦
82.5k●92●720●1273
accept rate: 23%

edited 03 Jan '17, 10:17