2

I have a problem with default values the are set in a PostgreSQL table. When I work on them in QGIS 2.8.1 and I save the layer, the default values were not computed, so the constraints block me. With QGIS 2.6 it works correctly. Anyone else have this problem? I haven't seen any bug report about this...

CREATE TABLE trasporti.strade_sentieri
(
  gid serial NOT NULL,
  cod_grid character varying(10) NOT NULL,
  cod_cat integer NOT NULL, 
  cod_layer character varying(5) NOT NULL, 
  descrizione character varying(200), 
  tipo_via character varying(50), 
  nome_via character varying(254), 
  segnavia character varying(20), 
  priorita character(1) NOT NULL DEFAULT 'f'::bpchar, 
  galleria character(1) NOT NULL DEFAULT 'f'::bpchar, 
  ponte character(1) NOT NULL DEFAULT 'f'::bpchar, 
  costruzione character(1) NOT NULL DEFAULT 'f'::bpchar, 
  geom geometry(LineString,32632),
  ciclabile character(1) NOT NULL DEFAULT 'f'::bpchar, 
  nome1_sentiero character varying(255),
  nome2_sentiero character varying(255),
  ciclo character varying(5), 
  nome1_ciclo character varying(255), 
  CONSTRAINT strade_sentieri_pkey PRIMARY KEY (gid),
  CONSTRAINT strade_sentieri_cod_grid_fkey FOREIGN KEY (cod_grid)
      REFERENCES base.tabacco_grid (cod_grid) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT strade_sentieri_cod_layer_fkey FOREIGN KEY (cod_layer, cod_cat)
      REFERENCES t_codici_tabacco (cod_layer, cod_cat) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT strade_sentieri_ciclabile CHECK (ciclabile = ANY (ARRAY['t'::bpchar, 'f'::bpchar])),
  CONSTRAINT strade_sentieri_ciclo CHECK (ciclo IS NULL OR (ciclo::text = ANY (ARRAY['SC005'::character varying::text, 'SC014'::character varying::text, 'SC018'::character varying::text]))),
  CONSTRAINT strade_sentieri_cod_cat CHECK (cod_cat = ANY (ARRAY[1, 2])),
  CONSTRAINT strade_sentieri_costruzione CHECK (costruzione = ANY (ARRAY['t'::bpchar, 'f'::bpchar])),
  CONSTRAINT strade_sentieri_galleria CHECK (galleria = ANY (ARRAY['t'::bpchar, 'f'::bpchar])),
  CONSTRAINT strade_sentieri_nome1_ciclo CHECK (nome1_ciclo IS NULL OR ciclo IS NOT NULL AND nome1_ciclo IS NOT NULL),
  CONSTRAINT strade_sentieri_nome1_sentiero CHECK (nome1_sentiero IS NULL OR segnavia IS NOT NULL AND nome1_sentiero IS NOT NULL),
  CONSTRAINT strade_sentieri_ponte CHECK (ponte = ANY (ARRAY['t'::bpchar, 'f'::bpchar])),
  CONSTRAINT strade_sentieri_priorita CHECK (priorita = ANY (ARRAY['t'::bpchar, 'f'::bpchar]))
)
WITH (
  OIDS=FALSE
);
ALTER TABLE trasporti.strade_sentieri
  OWNER TO tabacco;


-- Index: trasporti.strade_sentieri_cod_grid_idx

-- DROP INDEX trasporti.strade_sentieri_cod_grid_idx;

CREATE INDEX strade_sentieri_cod_grid_idx
  ON trasporti.strade_sentieri
  USING btree
  (cod_grid COLLATE pg_catalog."default");

-- Index: trasporti.strade_sentieri_cod_layer_idx

-- DROP INDEX trasporti.strade_sentieri_cod_layer_idx;

CREATE INDEX strade_sentieri_cod_layer_idx
  ON trasporti.strade_sentieri
  USING btree
  (cod_layer COLLATE pg_catalog."default");

-- Index: trasporti.strade_sentieri_descrizione_idx

-- DROP INDEX trasporti.strade_sentieri_descrizione_idx;

CREATE INDEX strade_sentieri_descrizione_idx
  ON trasporti.strade_sentieri
  USING btree
  (descrizione COLLATE pg_catalog."default");

-- Index: trasporti.strade_sentieri_geom_gist

-- DROP INDEX trasporti.strade_sentieri_geom_gist;

CREATE INDEX strade_sentieri_geom_gist
  ON trasporti.strade_sentieri
  USING gist
  (geom);

-- Index: trasporti.t_aggiornamento_grid_cod_grid_idx

-- DROP INDEX trasporti.t_aggiornamento_grid_cod_grid_idx;

CREATE INDEX t_aggiornamento_grid_cod_grid_idx
  ON trasporti.strade_sentieri
  USING btree
  (cod_grid COLLATE pg_catalog."default");


-- Trigger: t_storico_strade_sentieri on trasporti.strade_sentieri

-- DROP TRIGGER t_storico_strade_sentieri ON trasporti.strade_sentieri;

CREATE TRIGGER t_storico_strade_sentieri
  AFTER INSERT OR UPDATE OR DELETE
  ON trasporti.strade_sentieri
  FOR EACH ROW
  EXECUTE PROCEDURE storico_strade_sentieri();

This is the table where I have the problem. When I add a line, default values for 'priority', 'galleria', 'ponte' and 'costruzione' were not applied so, the NOT NULL constraints, blocks my save. I use this attributes for the rule-based style of the layer in QGIS.

I'm going to test this issue on another table as soon as possible.

4
  • 1
    Unable to duplicate this issue with a v2.8.1 OsGeo4w install on Windows 7. I tried this with a text column and an integer column, and in both cases QGIS loaded the default values (as defined from PostgreSQL) into the feature creation attribute form. You may need to add some info on what type of data, defaults and constraints you are working with.
    – Scro
    Mar 13, 2015 at 17:16
  • I have paste the create code for the table in the post.
    – Sergio
    Mar 16, 2015 at 7:36
  • I re-open this ticket because in the last nightly (2.8.1-42) the problem persist.
    – Sergio
    Apr 22, 2015 at 9:04
  • 1
    This is not a ticket; #12386 and/or #12391 are tickets.
    – Mike T
    Apr 22, 2015 at 9:24

1 Answer 1

2

QGIS is unable to use the default values that are cast as bpchar. So, a default value of ex. 'f'::bpchar is passed literally to the attribute form, and then inserted literally into the table. Obviously the test '''f''::bpchar' = ANY ('f'::bpchar, 't'::bpchar) will fail.

Defaults in the QGIS will work properly if you ALTER your column defaults to be simply, for example, 'f' instead of 'f'::bpchar. I do not know what any side effect of changing that would be... All I know of bpchar is that it is an internal data type that is equivalent to perhaps char(n), and so I would guess it generally should not be used.

Update

This appears to be a bug, and I have opened ticket #12391 on hub.qgis.org. I can confirm on QGIS 2.8.1, OsGeo4W built against PostgreSQL Client Version 8.3.10, that columns defined with fixed length strings -- char(n) or character(n) -- that have default values are not handled correctly in the feature creation attribute form. The Postgres internal casting to bpchar is passed literally to the text input box.

Sorry for the confusion, I should have realized that code you pasted was a pgAdmin (or such) script.


The Fix

This has now been fixed in the following commit, thanks to quick response by the QGIS devs:

https://github.com/qgis/QGIS/commit/bfb8ab6893d5bf77b4ae92c6c90e0b5b9c7e9ae7

5
  • In qgis 2.6 this default values are not a problem. It works perfectly. But I will try your solution. Thanks
    – Sergio
    Mar 17, 2015 at 6:04
  • I have no idea why the QGIS versions work differently, but it's almost certainly a bigger issue that you are casting your default values (and check constraints) to a different data type than the column definitions.
    – Scro
    Mar 17, 2015 at 12:29
  • I have tested your solution. 'f'::bpchar it's just a standard sintax for char(n). If you change in varchar it becames ::character varying so, in terms of syntax, it doesn't change so much... it's not possibile for what I have seen to set just 'f' as you say. (if you write alter table [..] DEFAULT 'f' it becames automaticaly 'f'::bpchar)
    – Sergio
    Mar 17, 2015 at 13:07
  • Okay, it just occurs to me that we are looking not at your actual CREATE statements, but a reverse engineered output such as pgAdmin. Will update answer.
    – Scro
    Mar 17, 2015 at 13:34
  • @Sergio - The bug has been fixed and should be available at next update or nightly build. Please accept this as the correct answer if it solves the issue for you.
    – Scro
    Mar 18, 2015 at 18:37

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.