Hi,

I did read https://www.sqlite.org/nulls.html and few other posts on mailing list, so I understand the curent status, and compatibility, as well the standard ambiguity.

I would like UNIQUE constraint to have option to treat NULLs on all involved columns / expressions, or some columns as distinct.

https://www.sqlite.org/nulls.html suggests to recompile sqlite with a modified macro, but that is not always possible, and would also apply to all unique constraints.

I have this DDT:

CREATE TABLE IF NOT EXISTS data (
  date text,
  time text,
  abbreviation_canton_and_fl text,
  ncumul_tested integer,
  ncumul_conf integer,
  ncumul_hosp integer,
  ncumul_ICU integer,
  ncumul_vent integer,
  ncumul_released integer,
  ncumul_deceased integer,
  source text,
  UNIQUE(date, time, abbreviation_canton_and_fl)
)

time can be NULL, however, I don't want to have two same entries if (date=X, time=NULL, abbreviation_canton_and_fl=CC).

I use INSERT OR REPLACE for inserting data.

The issue is that because time can be NULL, and 'source' column is almost always unique (it contains some timestamps), I can't use SELECT DISTINCT * FROM data; either, because DISTINCT operates on all columns, not just date, time, abbreviation_canton_and_fl.

So, my proposal would be to have something like this:

CREATE TABLE IF NOT EXISTS data (
  date text NOT NULL,
  time text,
  abbreviation_canton_and_fl text NOT NULL,
  ncumul_tested integer,
  ncumul_conf integer,
  ncumul_hosp integer,
  ncumul_ICU integer,
  ncumul_vent integer,
  ncumul_released integer,
  ncumul_deceased integer,
  source text,
  UNIQUE(date, time NULL_ALWAYS_DISTINCT, abbreviation_canton_and_fl)
)

WDYT?

Why not simply declare a default value?

CREATE TABLE IF NOT EXISTS data (
  date text NOT NULL,
  time text default 'N/A',
  abbreviation_canton_and_fl text NOT NULL,
  ncumul_tested integer,
  ncumul_conf integer,
  ncumul_hosp integer,
  ncumul_ICU integer,
  ncumul_vent integer,
  ncumul_released integer,
  ncumul_deceased integer,
  source text,
  UNIQUE(date, time, abbreviation_canton_and_fl)
);

Nulls are not a value. NULL means "any possible value within the domain, but I don't know which value" and therefore they are indistinct by very definition. If you want a value, put a value. If you want the default value to be something rather than unknown/missing (ie, NULL) then why cannot you do that?

That is a nice and obvious trick, and it works. Thank you!