Bug report #21460
Does not properly form SQL queries to insert or update into PostGIS
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.
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.
#5 Updated by Cory Albrecht over 5 years ago
- File Screenshot from 2019-03-10 11-22-25.png added
#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...
^
]