Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

When I enter new elements (features) in another Postgres layer, I can do it in two ways:

  1. Drawing new element(s) (with 'Add feature') which I do rarely or
  2. Copying (or cutting) some elements from another Postgre layer (Source layer), and paste it into target layer which I do frequently

In first example, saving of edits works normal because this layer gets gid from postgre database sequence *nextval('layer_name_gid_seq'::regclass)*

In second example, I got an error during saving edits, because while copying element from source layer to target layer qgis copied gid of element from source layer. When attempting to save edits this error is returned:

Could not commit changes to layer „Cjevovodi“
Errors: ERROR: 1 feature(s) not added.
Provider errors:
PostGIS error while adding features: ERROR: duplicate key value violates unique constraint "cjevovodi_okill_pkey"
DETAIL: Key (gid)=(5) already exists.

I tried to copy *nextval('layer_name_gid_seq'::regclass)* in field gid, but this sequence can`t be pasted in field gid as field is defined as numeric.

Does anyone know simple way to copy elements from source layer (with existing gid) assign new gid?

Thanks!

share|improve this question
    
What version of qgis and platform are you using? I'm using QGIS 2.0 in windows 7 and I don't seem to have problems in copy paste between postigs layers. –  Alexandre Neto Oct 25 '13 at 13:04
    
Did you ever find a solution to this problem? I am encountering the exact same issue. I do not want to have to put a trigger in place in PostgreSQL when it is a matter of QGIS not pulling default values when new features are created by copying others. –  Get Spatial Dec 19 at 0:30

2 Answers 2

I'm not able to reproduce this in QGIS 2.2 Windows, c3a2817.

If other versions behave differently, or if you're continuing to have this problem, you can probably set up a PostgreSQL trigger on the table as a workaround:

Using this example table:

CREATE TABLE testing (gid serial PRIMARY KEY, geom geometry(Polygon, 4326));

Here's a trigger function that will assign a new gid where needed:

CREATE OR REPLACE FUNCTION testing_insert_trigger()
RETURNS trigger AS
$$
BEGIN
IF EXISTS (SELECT 1 FROM testing WHERE gid = NEW.gid) THEN
    NEW.gid := nextval('testing_gid_seq'::regclass);
END IF;
RETURN NEW;
END;
$$ language 'plpgsql';

Binding the function to the table...

CREATE TRIGGER testing_insert 
BEFORE INSERT ON testing
FOR EACH ROW EXECUTE PROCEDURE testing_insert_trigger();

This will automatically assign new IDs where the gid already exists. For example, the following query will now duplicate all data in the table instead of failing:

INSERT INTO testing (SELECT * FROM testing);

Of course, this approach can compromise the intent of your primary key, so use it carefully.

share|improve this answer

Select features from source layer and save selected (Save selected as...) in shapefile. Add saved shapefile in QGIS project and open attribute table, then delete field "gid" and save shapefile. Select features in shapefile and copy them in your work layer.

share|improve this answer
    
Thanks for your answer but I'm trying to find an easier way to do it. –  hapa Mar 1 '13 at 20:03
1  
if you have table like gid serial, int i , you can just insert i and you get gid automaticly. i have some distant memory that it is possibly in QGIS , but i can't recall howto do it –  simplexio Jun 25 '13 at 8:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.