PostgreSQL trouble editing points / lines / polygons in QGIS

Question

I have a problem with PostgreSQL and QGIS:

• I can add points/lines/polygons and fillup field

But I can't :

• Delete points/lines/polygons
• Modify points/lines/polygons (move or modify field)

ex : dbname='Europe' host=localhost port=5432 user='postgres' sslmode=disable key='tid' estimatedmetadata=true srid=900913 type=Point table="public"."planet_osm_point" (way) sql=

QGIS is connected as admin level into database. With pdAdmin III I can do all (create, delete, modify...)

The PostgreSQL database is imported from OSM with osm2pgsql.

On other layer, like .shp, I don't have this issue.

Any idea ?

Config : Ubuntu 14.04 / PostgreSQL 9.4 / QGIS 2.10

Answer 1

The problem was that there is no primary key.

In pgAdmin do this request.

ALTER TABLE tableName ADD PRIMARY KEY (id);

Example for planet_osm_line table and setting osm_id column ,as primary key :

ALTER TABLE planet_osm_line ADD PRIMARY KEY (osm_id);

untill osm_id is unique.

Answer 2

The solution is to create a primary key, as already mentioned.

But by design, osm2pgsql does not garantee that the osm_id is unique. It can produce several rows with the same osm_id.

To edit planet_osm_* tables in QGIS, it is wise the add another id column, for example gid.

To add a unique gid column:

ALTER TABLE planet_osm_point ADD gid serial PRIMARY KEY;
ALTER TABLE planet_osm_line ADD gid serial PRIMARY KEY;
ALTER TABLE planet_osm_polygon ADD gid serial PRIMARY KEY;
ALTER TABLE planet_osm_roads ADD gid serial PRIMARY KEY;

QGIS has problems if the geometry column is just GEOMETRY. Some queries might fail. I found it useful to change the planet_osm_polygon geometry to MultiPolygon with:

ALTER TABLE planet_osm_polygon
 ALTER COLUMN way TYPE geometry(MultiPolygon,3857) USING ST_Multi(way);

Answer 3

I think this is a primary key issue. Your table should have one and that is recognized when you view the table through PGAdmin.

I had this same problem, but re-loaded the layer with a primary key field called ID (arbitrary) and all my edit tools are now active on the toolbar.

Using SQL you can add an OID column of 'serial' data type and create a primary key using this statement:

ALTER TABLE schema."Tablename"
ADD ID serial NOT NULL
ALTER TABLE schema."Tablename" ADD PRIMARY KEY (id);

Using a GUI, you can re-load your layer with a primary key that will be recognized by the database and thus PostGIS and QGIS:

• open DB Manager
• connect to your database
• choose 'import layer/file'
• check the 'Primary Key' box, and enter a name for the 'primary key' column that will store the unique ID's that will enable editing:

enter image description here

From there, go back to PGAdmin, connect to your database, and click on the table you've just re-created - you can now see if the OID column is recognized:

enter image description here