2

If I have a PostGIS database that has a table with an integer column that has a sequence that will normally autofill it, how do I get QGIS to not make me fill out that field (and not try to put NULL in it either)?

Everything I seem to do seems to make me either manually manage coming up with new "ID"s or try to put NULL in the column and then fails because the "ID" column is defined as being not NULL.

What do I need to do in QGIS to get it to not specific a value for that column and just let the database (PostGIS) fill it in?

This is with QGIS version 3.8.

This is the definition of one of the tables I'm having trouble with:

CREATE TABLE public.utility_pole (
    id integer NOT NULL,
    pole_owner text,
    pole_owner_primary_label text,
    latlong public.geometry(Point,4326) NOT NULL
);

ALTER TABLE public.utility_pole OWNER TO postgres;

ALTER TABLE public.utility_pole ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.utility_pole_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

ALTER TABLE ONLY public.utility_pole
    ADD CONSTRAINT utility_pole_id_pk PRIMARY KEY (id);
  • I am using the same concept and it works fine. When starting the editing mode the id-value "nextval('"schema"."layer_id_seq"'::regclass)" showes up an turnes into a serial id when saving the layer. Sometimes i had trouble when the the name of the sequence had changed, eg. to '"schema"."layer_id_seq1". So I renamed it to the original name and it worked again. – Maja Ott Jul 30 at 6:47
  • Which version of qgis are you using ? I would advice to get the LTR 3.4 if that's not what you are using as your issue reminds me of old version of qgis. As mentionned by Maja, the field should have the mention nextval in it – gael Jul 30 at 8:36
  • Are you by any chance using PostGIS (updatable)view not actual table? – Mat Jul 30 at 8:58
  • Is it defined as an auto incremental field ? I think the issue is rather on the postgres side ... could u join a screen shot of your attribute definition ? – snaileater Jul 30 at 9:15
  • Why not just use a id serial NOT NULL column and move on? The postgres login should not ever own any user tables. Your problems could all be due to failure to use best security practice. – Vince Jul 30 at 13:51
1

Use id serial NOT NULL column and enable Evaluate default values on provider side option in Project Properties -> Data Sources Properties.

Evaluate default values on provider side: When adding new features in a PostgreSQL table, fields with default value constraint are evaluated and populated at the form opening, and not at the commit moment. This means that instead of an expression like nextval('serial'), the field in the Add Feature form will display expected value (e.g., 25).

source: https://docs.qgis.org/3.4/en/docs/user_manual/introduction/qgis_configuration.html#data-sources-properties

  • 1
    Ok, so it sounds like my problem comes from QGIS not yet properly supporting the 'AS IDENTITY' syntax PostgreSQL is try to move toward. Right? (Is there any way to use the more future-proof syntax and not have to adjust/fix 40 some tables in my model?) – Azendale Jul 30 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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