2

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 all1) boolean columns are affected.

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

1 A few of my boolean columns are working fine. They seem identical2 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.

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

3 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;
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:

enter image description here

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

enter image description here

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...

  • First off, changing from t and f to true and false seems to have done the trick. After changing the widget's state values on one of my existing tables I no longer get the insert errors. For reasons unknown my copy of QGIS (2.16.0) crashes when adding a new point to the table created using the SQL you provided (maybe something to do with the table structure - I suspect it wants the geom column first); the crash occurs immediately upon clicking OK in the "Feature Attributes" window. Your solution works perfectly though and I'll accept your answer in a day or two unless a better one is posted. – Alex Hajnal Nov 7 '18 at 9:57
  • Thankfully, it looks like my existing rule-based styles still work after the change. They all use 't' for TRUE and 'f' for FALSE. I've got hundreds of them and really wasn't looking forward to having to change them all. – Alex Hajnal Nov 7 '18 at 10:03
  • 1
    With very little Python code you can tell QGIS to log to a file. QGIS might log the cause that caused the crash. See Matthias Kuhn answer in gis.stackexchange.com/questions/84790/… – Jakob Nov 7 '18 at 12:28
  • Thanks for the tip. Nothing got logged though. I might try running qgis under gdb when I have a free moment. The crash seems to be specific to my current project; that table works fine in an otherwise-empty project. – Alex Hajnal Nov 7 '18 at 12:39
  • QGIS 2 had a text representation of true and false (f and t), QGIS 3 natively supports them which means that it's also possible to use a boolean field directly in an expression (write "is_hidden" instead of "is_hidden" = 't') and it doesn't map the text 't' or 'f' to true or false because, well it's text and not a boolean after all :) For the migration: best to open the qgs file in a text editor and use some search/replace ;) – Matthias Kuhn Nov 7 '18 at 13:27
0

After some experimentation I managed to 'fix' the problem. The columns that had issues all had a default value set on the Postgres side. After removing the default value from the column, saving new features with FALSE boolean fields from QGIS works fine.

This does not, however, explain why this is happening. Having columns that are NOT NULL with sane defaults is often a Good Idea™ and I'd really like to find a solution that would let me keep them.

I'm intentionally not accepting this answer since it's a work-around and not a true solution to the problem.

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.