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!

Answer 1

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 *;

Answer 2

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.