I am trying to copy and paste point features from one table to an existing PostGIS table, but I get a NOT NULL, Unique error for the id field (below). How can I update the PostGIS table with new data created in QGIS?
QGIS version 3.22.4-Białowieża
-
2This is a bug github.com/qgis/QGIS/issues/46467 there is a solution but you don't give us the version of QGIS you are using.– Mapperz ♦Commented Sep 29, 2022 at 22:06
-
@Mapperz QGIS version 3.22.4-Białowieża. I tried the solution above and it works only if I manually enter the same id value that is in the id column.– MJMCommented Sep 29, 2022 at 22:16
-
1@MJM have you ticked the boxes 'Automatically create transaction groups ...' and 'Evaluate defaukt values on provider side' in the project properties / Data sources ?– dd_aCommented Sep 30, 2022 at 6:44
-
Is your id column set up as a serial using a sequence? If not, there's no 'next id' to get from the db so it will always be null.– jbalkCommented Sep 30, 2022 at 22:09
-
@dd_a yes I have ticked both boxes.– MJMCommented Sep 30, 2022 at 23:06
Add a comment
|
1 Answer
One way to do it, from the postgis side, is to add a new serial column and set it as the primary key:
alter table your_table add column pk serial primary key;
This will provide QGIS the next sequence value as the pk when you add new features.
You can also alter your existing id column like this:
create sequence your_table_id_seq;
alter table your_table alter column id set default nextval('your_table_id_seq');
select max(id) + 1 from your_table; -- use this value in the next part
alter sequence your_table_id_seq
minvalue 100 -- change this number to the max(id) + 1 value
start 100 -- also change this number
restart 100 -- and this number
You might also need to make the id column the primary key:
alter table your_table add primary key (id);
