Bug report #21460

Does not properly form SQL queries to insert or update into PostGIS

Added by Cory Albrecht over 5 years ago. Updated over 5 years ago.

Status:Open
Priority:Normal
Assignee:-
Category:Data Provider/PostGIS
Affected QGIS version:3.7(master) Regression?:No
Operating System:Ubuntu 18.10 Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:29277

Description

After editing a feature in a PostGIS and trying to save the layer, QGIS gives the warning that it could not commit the changes to the layer. The following error in the PostGIS messages tab.

2019-03-03T18:18:05     WARNING    Erroneous query: UPDATE "Balance of Power"."timeline-countries" SET "main_rgb"='E1BE48',"border_rgb"='D9B023',"info_url"=E'{""}',"flag_url"=E'{""}',"predecessors"=E'{""}',"successors"=E'{""}' WHERE "feature_uuid"::text='e0471e92-82e6-484c-92e2-1538eb6e0adb' returned 7 [ERROR: invalid input syntax for uuid: "" 
             LINE 1: ...fo_url"=E'{""}',"flag_url"=E'{""}',"predecessors"=E'{""}',"s...
              ^
             ]

The column it is complaining about is predecessors, which in PostGIS is an array of UUID. The column is empty, but QGIS appears to be trying to save it as an array with a single empty string but that column for that row is empty (NULL).

The schema for the table is:

CREATE TABLE "Balance of Power"."timeline-countries" 
(
    geom geometry(MultiPolygon,4326),
    name text COLLATE pg_catalog."default",
    start_date date,
    end_date date,
    main_rgb character varying(8) COLLATE pg_catalog."default",
    feature_uuid uuid NOT NULL DEFAULT uuid_generate_v4(),
    country_uuid uuid NOT NULL DEFAULT uuid_generate_v4(),
    stripe_rgb character varying(8) COLLATE pg_catalog."default" DEFAULT NULL::character varying,
    border_rgb character varying(8) COLLATE pg_catalog."default" DEFAULT NULL::character varying,
    border_shade numeric DEFAULT 0.085,
    sovereign text COLLATE pg_catalog."default",
    type text COLLATE pg_catalog."default",
    info_url text[] COLLATE pg_catalog."default" DEFAULT ARRAY[]::character varying[],
    flag_url text[] COLLATE pg_catalog."default" DEFAULT ARRAY[]::character varying[],
    predecessors uuid[] DEFAULT ARRAY[]::uuid[],
    successors uuid[] DEFAULT ARRAY[]::uuid[],
    visible boolean DEFAULT true,
    alternate_name text[] COLLATE pg_catalog."default",
    full_name text[] COLLATE pg_catalog."default",
    full_names json,
    CONSTRAINT "timeline-countries_pkey" PRIMARY KEY (feature_uuid)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

I cannot edit several fields (info_url, flag_ur, predecessors, successors) in the form view for the feature that cannot be updated, but can do so for other features.

Screenshot from 2019-03-10 11-22-25.png (136 KB) Cory Albrecht, 2019-03-10 04:26 PM

History

#1 Updated by Cory Albrecht over 5 years ago

Also happens in 3.6.0 and 3.7

#2 Updated by Giovanni Manghi over 5 years ago

  • Priority changed from High to Normal

#3 Updated by Giovanni Manghi over 5 years ago

  • Affected QGIS version changed from 3.4.5 to 3.7(master)

#4 Updated by Cory Albrecht over 5 years ago

I've now had this error occur in 3.45,3.60 and 3.7 for all of the array columns that are allowed to be null. This wouldn't be quite so frustrating if the feature attributes form or the attribute table would let me edit them, but they will not let me type in those boxes. I have to use the field calculator.

I've also had a several occurrences where QGIS has seemingly put in the the fields the text for the SQL data types of the columns in question.

#6 Updated by Cory Albrecht over 5 years ago

Bump.

#7 Updated by Cory Albrecht over 5 years ago

Cory Albrecht wrote:

Bump.

And again.

2019-04-01T18:02:21 WARNING Erroneous query: UPDATE "Balance of Power"."timeline-countries" SET "info_url"=E'{""}',"flag_url"=E'{""}',"predecessors"=E'{""}',"successors"=E'{""}',"visible"=TRUE WHERE "feature_uuid"::text='2a4f7873-5df6-44b7-a2fa-ab5556edc14e' returned 7 [ERROR: invalid input syntax for uuid: ""
LINE 1: ...fo_url"=E'{""}',"flag_url"=E'{""}',"predecessors"=E'{""}',"s...
^
]

Also available in: Atom PDF