QGIS – Troubleshooting Feature Creation with FALSE Boolean Fields in QGIS and Postgres

Question

I'm having problems with adding rows that have boolean columns to Postgres tables from QGIS. In a nutshell, shapes with boolean fields can only be created when the booleans are set to true (updates work fine). Update: The only columns that fail are those with default values on the DB side.

Note: I have found a work-around but would like a proper solution (see my answer below)

In QGIS, the boolean fields have "Check Box" as their edit widget with t for the checked value and f as the unselected value. Creating a new shape and saving it results in errors for the boolean columns that are false since the field value is not being quoted unless the value is true. Subsequent updates work fine though (including setting the value to false). As best I can tell most (but not all¹) boolean columns are affected.

Does anyone know a fix for this?³ I'm going absolutely batty with having to set the booleans true then go back and set them false after saving.

¹ A few of my boolean columns are working fine. They seem identical² to the ones that are failing on both the QGIS and the Postgres side. Oddly, one of my tables has ~7 boolean columns of which only two exhibit the problem. This plus the logs make me think the issue is on the QGIS side.

² Actually, they're not identical. All of the failing columns have default values on the DB side.

³ I found a work-around but not a true solution (see my answer below)

Versions:

QGIS 2.16.0
Postgres 9.5.2 (64-bit)

Error messages, etc. for one of the affected tables follow. I'm seeing the same issue with all of the tables I'm using that have boolean columns.

The error reported by QGIS is:

Could not commit changes to layer ny_wetlands_modified

Errors: ERROR: 1 feature(s) not added.

  Provider errors:
  PostGIS error while adding features: ERROR:  column "f" does not exist
    LINE 1: ...,$2,NULL,NULL,NULL,'Riverine',NULL,NULL,NULL,NULL,f) RETURNI...

The Postgres log shows the following:

ERROR:  column "f" does not exist at character 264
STATEMENT:  INSERT INTO "public"."ny_wetlands_modified"("geom","gid","attribute","hgm_code","qaqc_code","wetland_ty","acres","hist_attri","shape_leng","shape_area","is_hidden") VALUES (st_multi(st_geomfromwkb($1::bytea,4326)),$2,NULL,NULL,NULL,'Riverine',NULL,NULL,NULL,NULL,f) RETURNING "gid"

Setting the field to true using the checkbox and then saving succeeds with the log showing:

LOG:  execute addfeatures: INSERT INTO "public"."ny_wetlands_modified"("geom","gid","attribute","hgm_code","qaqc_code","wetland_ty","acres","hist_attri","shape_leng","shape_area","is_hidden") VALUES (st_multi(st_geomfromwkb($1::bytea,4326)),$2,NULL,NULL,NULL,'Riverine',NULL,NULL,NULL,NULL,'t') RETURNING "gid"

Now the field can be set false (unchecked) resulting in:

LOG:  statement: UPDATE "public"."ny_wetlands_modified" SET "is_hidden"='f' WHERE "gid"=27650

The definition of the boolean column for this particular table is:

ALTER TABLE public.ny_wetlands_modified ADD COLUMN is_hidden boolean;
ALTER TABLE public.ny_wetlands_modified ALTER COLUMN is_hidden SET DEFAULT false;

Answer 1

I have no problem using default values from QGIS 2.18.24 and QGIS 3.4.0 when mapping a boolean postgres default field in QGIS to a Checkbox.

The representation state of for checked and unchecked state should be true and false. Not sure t and f would work:

Actually QGIS 3.4.0 has hardcoded TRUE and FALSE as representation state:

The SQL I tested on:

drop table if exists mytable;
create table mytable (id serial primary key, is_hidden boolean default true not null, geom geometry(point, 25832));
insert into mytable(geom) values (ST_GeomFromText('POINT(1000 10000)', 25832));

Then set the widget and do some tests...