2

I have a simple PostGIS table :

CREATE TABLE public.elem_p
(
    gid integer NOT NULL DEFAULT nextval('elem_p_gid_seq'::regclass),
    liste character varying[] COLLATE pg_catalog."default" NOT NULL,
    geom geometry(MultiPolygon,2154),
    CONSTRAINT elem_p_pkey PRIMARY KEY (gid)
)

The 'liste' field is array-type ;

When I split polygons one-by-one with the QGIS splitting tool, there is no problem.

When I split several polygons that all have at least 2 dimension in the array type "liste", there is no problem ...

BUT when I split several polygons that have less than 2 dimensions in the array type "liste" (0 or 1) I have an error like this :

Could not commit changes to layer elem_p

Errors: SUCCESS: 2 geometries were changed. ERROR: 2 feature(s) not added. Provider errors: PostGIS error while adding features: ERREUR: tableau litéral mal formé : « abcd » DETAIL: La valeur du tableau doit commencer avec « { » ou avec l'information de la dimension.

(raw translation from french : "wrongly build array : "abcd" ; DETAIL : array value must start with "{" or with information about dimension)

enter image description here

EDIT : The whole new script :

/* TABLE */ 
DROP TABLE IF EXISTS elem_p CASCADE ; CREATE TABLE elem_p (gid serial, liste varchar[], geom geometry(multipolygon, 2154)) ;
INSERT INTO elem_p (gid, liste, geom) 
VALUES (nextval('elem_p_gid_seq'::regclass), '{E-1, E-5}', ST_MULTI(ST_SETSRID(st_geomfromtext('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))'), 2154)))
   ,(nextval('elem_p_gid_seq'::regclass), '{E-1, E-7}', ST_MULTI(ST_SETSRID(st_geomfromtext('POLYGON((10 10, 6 6, 6 7, 6 8, 7 10 , 9 10, 10 10))'), 2154)))
   ,(nextval('elem_p_gid_seq'::regclass), NULL, ST_MULTI(ST_SETSRID(st_geomfromtext('POLYGON((0 10, -3 10, -3 8, -5 5, 0 4, 0 10))'), 2154))) ;
UPDATE elem_p  SET liste = '{E-30}'  WHERE gid = 2 ; 

/* VIEW  */
CREATE OR REPLACE VIEW v_elem_p AS (SELECT * FROM elem_p) ;

/*  FUNCTION  */
DROP FUNCTION IF EXISTS maj_elem_p() CASCADE ;
CREATE FUNCTION maj_elem_p() RETURNS TRIGGER AS 
$$   BEGIN 
IF TG_OP = 'INSERT' THEN INSERT INTO elem_p (liste, geom)  VALUES(COALESCE((NEW.liste::varchar)::varchar[], '{}'::varchar[]), NEW.geom); RETURN NEW; END IF;
IF TG_OP = 'UPDATE' THEN UPDATE elem_p SET (liste, geom) = (COALESCE((NEW.liste::varchar)::varchar[], '{}'::varchar[]), NEW.geom) WHERE gid = NEW.gid; RETURN NEW ; END IF ;
END;  
$$  LANGUAGE PLPGSQL ;

/* TRIGGER  */ 
DROP TRIGGER IF EXISTS tg_maj_elem_p  ON v_elem_p ; CREATE TRIGGER tg_maj_elem_p INSTEAD OF INSERT OR UPDATE ON v_elem_p  FOR EACH ROW   EXECUTE PROCEDURE public.maj_elem_p();

/* TESTS */
UPDATE v_elem_p  SET liste = '{E-99}'  WHERE gid = 2 ; 
INSERT INTO v_elem_p VALUES (DEFAULT, '{E-123, E-54}', ST_MULTI(ST_SETSRID(st_geomfromtext('POLYGON((3 3, 5 3, 5 4,5 5, 2 6, 3 3))'), 2154)))
4
  • 1
    dimensions should rather be cardinality, or simply length; the column is defined as 1D-array. However, I dare say this is a bug in QGIS' way of (de)serializing and mapping of its internal array type to the PostgreSQL array type and vice versa. It sounds like an array with a single member is just not considered an array when mapped. – geozelot Oct 21 '20 at 9:33
  • Thanks for your answer. Do you know any workaround to deal with that 'bug' ? – A. Jean Oct 21 '20 at 12:01
  • Well, you could work with a View that acts as a proxy between QGIS and PG and that holds a concatenated string of the array values, with a trigger in place to transform between string and array of strings. I'm not sure rn if a BEFORE trigger, or a RULE on the table itself would be enough to coerce values into the array prior to upserting. However, the most appropriate solution would be to normalize that table into a geometry table and a related attribute table, since you effectively duplicate the liste values for each split geometry. – geozelot Oct 21 '20 at 17:42
  • Thanks to @geozelot answer, I tried to build a VIEW, wich has a trigger "INSTEAD OF" ; the TRIGGER FUNCTION is supposed to transform 'on the fly' the array to a string (to deal with the 'QGIS bug') and then to coerce back to an array (see the whole script in the edit in my initial post). It doesn't solve the problem of the use of the 'cut polygon' in QGIS (cf. description in first post) ... – A. Jean Oct 28 '20 at 9:19
1

Picking up the comments, and referring to your edit:

In order to workaround the apparent bug with a proxy View you will make it hold a string column rather than the original array, or the same issues will arise!

Use

CREATE OR REPLACE VIEW elem_p_v AS
    SELECT  gid,
            ARRAY_TO_STRING(liste, ',') AS liste,
            geom
    FROM    elem_p
;

CREATE OR REPLACE FUNCTION elem_p_v_insert_func()
  RETURNS TRIGGER AS
    $$
    BEGIN
      INSERT INTO elem_p (liste, geom) VALUES
        (REGEXP_SPLIT_TO_ARRAY(NEW.liste, ','), NEW.geom)
      ;
      RETURN NEW;
    END;
    $$
  LANGUAGE 'plpgsql'
;

CREATE TRIGGER elem_p_check_array_literal
  INSTEAD OF INSERT ON elem_p_v
  FOR EACH ROW
  EXECUTE PROCEDURE elem_p_v_insert_func()
;

instead. The View will hold a string, concatenated from the array (change the concatenator if needed, but remember to change the regexp pattern in the trigger function accordingly).

Needless to say that you then need to work with a single string in QGIS rather than an array.

Note that in this particular case (splitting geometries), the issue arises for the actual INSERT operation only. As long as you don't change the liste values, you don't need an UPDATE handler.


This could be handled with a custom CAST, but since it requires to coerce an initial undecorated string literal (as UNKNOWN pseudo-type) into VARCHAR within a function, it would require a C conversion function; higher level language functions do not accept pseudo-types as of yet. Since adding casts can also have plenty of unforeseen consequences, I'll not add that here.

3
  • Useful answer, and it works well if I create a View without array-type ... The problem is that I need it to be array-type, because it is used by a Python plugin I made (or I would have to re-code part of the extension, and I'm not very keen on doing that ...). – A. Jean Oct 30 '20 at 14:45
  • @A.Jean It's a workaround, and it may be worth to open a bug report on QGIS bug tracker. There is no way to have PostgreSQL accept non-coercable data types for a column. You can, however, work with the View to clip and update your geometries, but feed the base table to your extensions. – geozelot Oct 30 '20 at 15:49
  • OK, I will manage it 'as it is' ... Thank you for your efforts ; I'm not yet used to bug reports, but I may try to do this... Thanks again. – A. Jean Oct 30 '20 at 16:15

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.