1

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

enter image description here

5
  • 2
    This 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
    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.
    – MJM
    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_a
    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.
    – jbalk
    Sep 30, 2022 at 22:09
  • @dd_a yes I have ticked both boxes.
    – MJM
    Sep 30, 2022 at 23:06

1 Answer 1

1

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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