Allowing NULL value in field's value map in QGIS?

Question

Value maps have been set for some attributes in the properties of a layer (which is an offline edit of a PostGIS database saved as a spatialite file).

But in the drop down selection it does not allow for a Null to be selected. So if a user selects one of them, there is no way to undo the selection.

I have tried entering both Null, and NULL, into the value map, and having an extra blank row.

The extra blank row does give me a blank option,

to give

but the value in the field is '' rather than NULL.

And selecting NULL gives me 'NULL' with the single quotation marks

Edit 1

In response to answer Bhaskar

When I run this SELECT query, I would like to have just these 4 values

not,

How can set it up so that a user can essentially deselect a value and return it to its previous NULL state?

EDIT 2

I have tried to run the SQL suggested by Bhaskar to just swap out a inserted NULL through the value map.

INSERT INTO awrtracker.awr_tracker_sync (threat_lvl) VALUES (NULL) where threat_lvl is 'NULL';

In place of the final 'NULL' in the expression I have tries 'Null' and 'null'. But the error is the same "ERROR: syntax error at or near "where".

It's an SQL problem isn't it, from what I have researched the WHERE statement is invalid here for PostgreSQL?

Answer 1

Implemented the value maps of Edit Widget Property on one of Reservoirs layer. I have an attribute with type of reservoir (this water body could be Reservoir, Dam, Anicut or even null) and I had no problem with it.

Initially, I clicked on Load Data From Layer button and selected my layer from the Layer dropdown. Then selected type attribute for both Value and Description. The important thing was to check the Insert Null value on top checkbox. this will actually automatically add null values to the list.

After clicking Ok, I got the values as follows:

Then you will be able to select values from the dropdown with null values as well. had no problem changing from null to values and from values to null.

Here is a video of what I did, Gives a clear picture how I did it. :

https://youtu.be/qpJ9Etlop-Y

With reference to your further edits in question and with my 10 years of software development experience, the inference i could make from the Value Map is that it cannot be changed with respect to the insert statement that its making with null values. The tool has been programmed to insert the null values as string. Unless and until someone is capable of reprogramming the tools to their required needs, the null insertion cannot be changes. If i am wrong then someone can correct me. But there is an alternative.

As you mentioned you only need to display other values except NULLs in the select query statement, there is a workaround. what you need to do is to initially change the NULL as string to null through UPDATE statement and then execute your select query.

first run the insert statement and then execute the select query. then you will get the desired results as you mentioned.

Have recreated the situation with the data I have with reservoirs. Actually you need to implement update statement instead of insert. I inserted null values into reservoir database from the dropdown created using value maps. have a look at the two tables in the snapshot below:

Here is a query I ran after updating the null values from the dropdowns. you can notice that null values have been displayed for results section of the query for distinct values:

Then I updated the database using update statement like in the snapshot and then ran the sql statement for distinct values. Got the results like desired and like displayed in the figure below:

Change your sql statement like this:

UPDATE awrtracker.awr_tracker_sync SET threat_lvl=null where threat_lvl='NULL';