Attribute Form Value Relation Doesn't Respect Column Default

[ScottWerkema]

Describe the bug
In 3.18, when digitizing, the attribute form doesn't respect the column default when the attribute is a value relation. In our case, this is when the layer is a Postgres/PostGIS layer. The attribute form defaults to the lower int value in the relation table rather than the column default in Postgres.

How to Reproduce

In Postgres

CREATE TABLE public.status
(
    status_code smallint NOT NULL,
    status_value text NOT NULL,
    CONSTRAINT status_pkey PRIMARY KEY (status_code)
);

INSERT INTO public.status (status_code, status_value) VALUES
(0, 'Closed'),
(-32767, 'Unknown'),
(1, 'Open');

CREATE TABLE public.feature_layer
(
    id bigserial,
    name text NOT NULL,
    status smallint NOT NULL REFERENCES public.status(status_code) ON DELETE RESTRICT ON UPDATE RESTRICT DEFAULT 1,
    geom geometry(Polygon,3857) NOT NULL
);

In QGIS 3.18

1. Add the public.feature_layer and public.status tables as layers in your project
2. In the layer properties for public.feature_layer configure a value relation to the public.status table.

3. Digitize a new feature on `public.feature_layer`. Note when the attribute form presents, the default value will be `Unknown` which is `-32767` rather than the default `Open`/`1`.

QGIS and OS versions
3.18.0 macOS 11.2.2

Additional context
In previous versions of QGIS, the Postgres column default is respected.

[gioman]

@ScottWerkema confirmed.

Also is not clear to me why on 3.18 after filling the "name" attribute the "save edits" button is not active and edits can be saved only by toggling editing off.

To note that on 3.16 the evaluation of the default value works as expected but in this example QGIS does not allow to edit/write in the "name" attribute, so saving edits is not possible (because of the "not null" constraint).

So BOTH 3.16 AND 3.18 are bugged.

[elpaso]

Cannot reproduce on current master:

[elpaso]

3.18.3-Zürich also works for me.

[gioman]

Works for me too now on 3.18.3

[github-actions]

The QGIS project highly values your report and would love to see it addressed. However, this issue has been left in feedback mode for the last 14 days and is being automatically marked as "stale".
If you would like to continue with this issue, please provide any missing information or answer any open questions. If you could resolve the issue yourself meanwhile, please leave a note for future readers with the same problem and close the issue.
In case you should have any uncertainty, please leave a comment and we will be happy to help you proceed with this issue.
If there is no further activity on this issue, it will be closed in a week.

[github-actions]

While we hate to see this happen, this issue has been automatically closed because it has not had any activity in the last 42 days despite being marked as feedback. If this issue should be reconsidered, please follow the guidelines in the previous comment and reopen this issue.
Or, if you have any further questions, there are also further support channels that can help you.

[Brent-Edwards]

This doesn't seem to be fully resolved in QGIS 3.22.

I have a Postgres table with boolean columns and default values (true or false).

When the default is set the, the check boxes don't appear when editing the values in QGIS. The only way to edit/update the values is through the Field Calculator or the Update Filtered/Selected buttons.

If I drop the default value, the check boxes appear and are editable through the usual methods in QGIS.

Here's a sample column def:

col_name boolean DEFAULT true

ALTER statements for test purposes:

ALTER TABLE schema_name.tab_name ALTER COLUMN col_name DROP DEFAULT;
ALTER TABLE schema_name.tab_name ALTER COLUMN col_name SET DEFAULT true;

[gioman]

This doesn't seem to be fully resolved in QGIS 3.22.

@Brent-Edwards works fine here on 3.22.1

verdade boolean DEFAULT true

[Brent-Edwards]

@gioman - Thanks for the reply. I just tried with 3.22.1 and QGIS is still behaving as stated in my previous message.

Without the default - checkbox appears on the edit form:

With the default (Postgres... COLUMN bool_def_t SET DEFAULT true) - no checkbox on the edit form:

Here's my complete QGIS setup info:

QGIS version
3.22.1-Białowieża
QGIS code revision
663dcf8
Qt version
5.15.2
Python version
3.9.5
GDAL/OGR version
3.4.0
PROJ version
8.2.0
EPSG Registry database version
v10.038 (2021-10-21)
GEOS version
3.10.0-CAPI-1.16.0
SQLite version
3.35.2
PDAL version
2.3.0
PostgreSQL client version
13.0
SpatiaLite version
5.0.1
QWT version
6.1.3
QScintilla2 version
2.11.5
OS version
Windows 10 Version 2004

Active Python plugins
changeDataSource
3.1
ImportPhotos
2.2.2
LAStools
1.4
latlontools
3.5.0
leastcostpath
1.0
Mergin
2020.6.1
processing_fusion
3.0
profiletool
4.1.8
qgis-maptiler-plugin
1.1.4
QPackage
1.5
QuickMultiAttributeEdit3
version 3.0.3
quick_map_services
0.19.11.1
splitmultipart
1.0.0
valuetool
3.0.10
db_manager
0.1.20
grassprovider
2.12.99
processing
2.12.99
sagaprovider
2.12.99

[gislars]

I can confirm this issue with current QGis-LTR.

A boolean column with DEFAULT value set isn't recognized as boolean (meaning: no checkbox is offered).
When I drop the DEFAULT, QGIS is showing the checkbox.

QGIS version | 3.28.12-Firenze | QGIS code revision | dcc633b2de5
-- | -- | -- | --
Qt version | 5.15.3
Python version | 3.10.12
GDAL/OGR version | 3.4.1
PROJ version | 8.2.1
EPSG Registry database version | v10.041 (2021-12-03)
GEOS version | 3.10.2-CAPI-1.16.0
SQLite version | 3.37.2
PDAL version | 2.3.0
PostgreSQL client version | unknown
SpatiaLite version | 5.0.1
QWT version | 6.1.4
QScintilla2 version | 2.11.6
OS version | Linux Mint 21.2

3.28.12-Firenze
QGIS code revision
[dcc633b2de5](https://github.com/qgis/QGIS/commit/dcc633b2de5)
Qt version
5.15.3
Python version
3.10.12
GDAL/OGR version
3.4.1
PROJ version
8.2.1
EPSG Registry database version
v10.041 (2021-12-03)
GEOS version
3.10.2-CAPI-1.16.0
SQLite version
3.37.2
PDAL version
2.3.0
PostgreSQL client version
unknown
SpatiaLite version
5.0.1
QWT version
6.1.4
QScintilla2 version
2.11.6
OS version
Linux Mint 21.2