Storing unknown: why null is not 0

Three answers

Does a company have a webshop? The answer is yes, no or we don't know. A field with two states can only hold two. If you pick false as the default, we don't know quietly becomes no.

The same goes for numbers and lists. Zero open vacancies is not the same as a number you don't know, and an empty list is not the same as a list you don't know.

Meaning	Value
Known	the value
No, zero or none	`false`, `0` or `[]`
Unknown	`null`

In your database

Let the column allow null, and give it no default value:

create table companies (
  kvk_number     char(8) primary key,
  webshop        boolean,  -- null: unknown
  open_vacancies integer,  -- null: unknown
  known_count    integer not null
);

Counting with unknown

avg and count(column) skip null. That is what you want: the average covers the companies you know it for.

-- the average of the companies we know it for
select avg(open_vacancies) from companies;

-- wrong: unknown now counts as zero
select avg(coalesce(open_vacancies, 0)) from companies;

count(*) counts the rows, including those with null. Put the two side by side and you see how much you know.

In your code

A check like if (!company.webshop) treats null as false. So compare exactly:

if (company.webshop === null) show('unknown')
else if (company.webshop) show('webshop')
else show('no webshop')

On screen

Show unknown as the word unknown. A dash or a blank cell looks like no, or like an error.