Bug report #9489

Copy/Paste issue with explicit primary keys

Added by Fabrice Fabrice almost 11 years ago. Updated almost 10 years ago.

Status:Closed
Priority:Severe/Regression
Assignee:-
Category:Vectors
Affected QGIS version:2.0.1 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:Yes Copied to github as #:18080

Description

Copy/paste from a shapefile to a Postgis table with explicit primary keys is no longer possible with QGis 2.0
(Explicit key, ie PostgreSQL primary key is not a serial but an explicit string or integer).

During the copy / paste, QGis 2.0 puts the primary keys values to NULL and no commit is possible.
With QGis 1.7.4, those copy / paste where possible.

To reproduce the pb :

Create table1 with a string primary key

CREATE TABLE table1
        (
          f1 character varying(10) NOT NULL,
          f2 character varying(10),
          geom geometry,
          CONSTRAINT t1_pkey PRIMARY KEY (f1),
          CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2),
          CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POINT'::text),
          CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 2154)
        )
        WITH (OIDS=TRUE);

table2 is the same as table 1 but it has no primary key

CREATE TABLE table2
        (
          f1 character varying(10) NOT NULL,
          f2 character varying(10),
          geom geometry,
          CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2),
          CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POINT'::text),
          CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 2154)
        )
        WITH (OIDS=TRUE);

shapes.shp is a shapefile that contains the points I would like to copy/paste in the Postgis tables
(see attached zip file)

Many Thanks for solving this blocking issue for us !
Fabrice

shapes.zip (1.33 KB) Fabrice Fabrice, 2014-02-04 04:38 AM

Related issues

Related to QGIS Application - Bug report #5758: Primary key issue when using merge attributes of selected... Closed 2012-06-07
Related to QGIS Application - Bug report #13755: Copy/paste PostGIS layer: date not autofill ( ('now'::tex... Closed 2015-11-04

Associated revisions

Revision 588d5f42
Added by Jürgen Fischer over 10 years ago

paste features: don't skip primary key fields, use default value if present,
otherwise value of pasted feature (fixes #9489)

History

#1 Updated by Giovanni Manghi over 10 years ago

  • Priority changed from Severe/Regression to High

#2 Updated by Giovanni Manghi over 10 years ago

  • Priority changed from High to Severe/Regression

#3 Updated by Giovanni Manghi over 10 years ago

  • Category changed from Browser to Vectors

#4 Updated by Jürgen Fischer over 10 years ago

  • Status changed from Open to Closed

#5 Updated by Gavin Fleming about 10 years ago

I had this issue in 2.4 where pasting a record overwrote the PostgreSQL default value which resulted in the commit complaining about duplicate gids. So in the layer Fields properties I set gid to non-editable. This seemed to protect the field from pasting and resulting in a successful commit.

#6 Updated by rmercer - almost 10 years ago

Gavin, thank you for putting in that tip. It was very frustrating to be having this same issue with no clue how to get around it. I am not really clear on the terminology with the bug reporting, so by having it changed from open to closed and fixed in a changeset, does this mean that a release after 2.6.1 will contain a fix for this issue?

If that is not the case, and it is assumed to be fixed, I want to update that this is still a problem. If you have a field defined as a primary key, or a field with a default value, when you copy a feature from another layer, or make a copy of an existing feature into this layer, the primary key and/or default value fields are set to NULL. When you try to save changes, an error is raised due to violating the primary key constraint. Is there a way to set the process of copying a new feature so it is treated the same way as a new feature being digitized and any default value conditions are inserted automatically?

It would be really helpful if documentation of Gavin's tip were included somewhere. It took me a number of hours of fruitless searching and confusion before I happened upon this site. My fault for not checking here first, though it probably wouldn't occur to a newer QGIS user at all.

#7 Updated by Giovanni Manghi almost 10 years ago

rmercer - wrote:

Gavin, thank you for putting in that tip. It was very frustrating to be having this same issue with no clue how to get around it. I am not really clear on the terminology with the bug reporting, so by having it changed from open to closed and fixed in a changeset, does this mean that a release after 2.6.1 will contain a fix for this issue?

the fix is 10 month old, so is already in qgis 2.6.1

If that is not the case, and it is assumed to be fixed, I want to update that this is still a problem. If you have a field defined as a primary key, or a field with a default value, when you copy a feature from another layer, or make a copy of an existing feature into this layer, the primary key and/or default value fields are set to NULL. When you try to save changes, an error is raised due to violating the primary key constraint. Is there a way to set the process of copying a new feature so it is treated the same way as a new feature being digitized and any default value conditions are inserted automatically?

I just tested copying a feature from a shapefile and pasting it into a postgis layer: on paste the postgis primary key is set to NULL, but on save is given the proper (serial) value.

Also available in: Atom PDF