QGIS 2.8 – Setting PostgreSQL Default Values in QGIS 2.8.1 Projects

postgispostgresqlqgis-2.8

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.

Best Answer

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

Related Question