SQL how to store meaningful null values?

Question

My column needs to store the output of a function that returns an integer or null, where null is a meaningful value such as 'unknown', while also having a null value for the column to mean unset/missing.

If the value were a three-valued boolean instead, I could see a potential solution being the use of a tinyint, where 0 codes for False, 1 for True, and 2 for 'unknown'; e.g. myCol TINYINT NULL. But in the above case, the full range of the integer is unavailable for use, so to say.

Thus, the desired data type supports values: null, null('unknown'), and integer.

Is there a way to achieve this in a single column? If not, what would be the ideal solution? I guess adding another column. Altering the function in question is not an option unfortunately. Thanks.

Edit: thanks for the insights/solutions!

You used both MySQL and PostgreSQL tags - which one do you use? PostgreSQL [`numeric`/`decimal`](https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL) can hold anything `smallint`, `int`, `bigint` can, plus special values `NaN`, `Infinity`, `-Infinity`. — Zegarek, Mar 15 '23 at 08:46
Are you saying that if the function is invoked and returns null (actual null not string 'null') then you want to transform null to unknown and store..How is the function invoked? — P.Salmon, Mar 15 '23 at 08:53
What meaning do you have in mind for `null` in this case? For numeric or float values, maybe `infinity` or `NaN` would be an option? — , Mar 15 '23 at 08:56
There have been some usages of multiple nulls indicating different "states" of nullness in relational theory but none of those have fed through into the SQL language, where we thankfully only have to deal with three-valued logic. I'm not aware of any commercial database system that has explored this area. — Damien_The_Unbeliever, Mar 15 '23 at 09:00
@Zegarek I was undecided on which of the two to use, hence the tags. I'm gonna look that! — Starbuck, Mar 15 '23 at 11:34
@P.Salmon The function returns an actual null, which by design is supposed to be meaningful. It wouldn't have to be transformed per se, it'd just have to be stored as a particular value (preferentially as part of the same column), like in the three-valued boolean example. — Starbuck, Mar 15 '23 at 11:42
@Starbuck I checked MySQL in the meanwhile and it sounds like while it has some kind of support for NaN, Inf, -Inf, you can't really use them conveniently. If you're really determined to keep this in a single column, you *could* also use the data type limit of any type you choose. If those are status codes, ports or sockets, they usually end way below int or bigint limit. — Zegarek, Mar 15 '23 at 11:44

Zegarek · Answer 1 · 2023-03-20T19:34:42.750

As per the comment: PostgreSQL numeric/decimal can hold anything smallint, int, bigint can, plus special values NaN, Infinity, -Infinity, as well as the usual null. You can use any combination of those: demo

create table your_function_output(result numeric);
insert into your_function_output values
   ('-Infinity'),
   (-1),
   (0),
   (1),
   (2.001),
   ('Infinity'),
   ('NaN'),
   (null)
returning *;

int[], an integer array type could also map the statuses

"No result", "empty result", "int result", to
A null, an empty array, an array with result
Or an empty array, an array with a null in it, then an array with a result: demo

create table your_function_output(result int[]);
insert into your_function_output values
   (       null         ),--plain null
   (ARRAY[ null ]::int[]),--an array with a null in it
   (ARRAY[      ]::int[]),--an empty array
   (ARRAY[  1   ]::int[]),--single-element array
   (ARRAY[1,2,3 ]::int[]),--multiple-element array
   (ARRAY[1,-5,2]::int[]),--different order
   (ARRAY[1,null]::int[]) --array with a null as one of the elements
returning *;

score 3 · Answer 2 · answered Mar 15 '23 at 09:14

When I have modelled systems like this previously, I've stored a flag that records whether the function has executed in one column and the result in a second column. E.g. using SQL Server syntax it could be:

CREATE TABLE T (
      ....,
      FunctionHasExecuted bit not null,
      FunctionResult int null,
      constraint CK_Optional_Bonus CHECK (FunctionHasExecuted = 1 OR FunctionResult IS NULL)
)

And then you can easily distinguish between the two conditions that cause the result to be null.

Substitute in an appropriate bool-like type instead of bit for your database system and choose whether or not to implement the CHECK constraint, if your database system supports that.

SQL how to store meaningful null values?

2 Answers2