1

I have a table specifying types to distinguish between scalars and lists. When "scalar" exists the value is a scalar and when it is null it is a list.

drop table type1 cascade;
create table type1 (
  id     integer primary key,
  scalar "char",
  name   text,
  unique (id, scalar)
);
insert into type1 values
  (1, 'x', 'single'),
  (2, null, 'multi');

Now I have a table with values, which references the type and adds a unique constrain on the tuple (type, scalar). This works, because NULL is distinct. If "scalar" is NULL it is possible to have more than one row per "type", because all "scalar"s are distinct.

drop table value1 cascade;
create table value1 (
  id     integer primary key,
  type   integer,
  scalar "char",
  value  text,
  foreign key (type, scalar) references type1 (id, scalar),
  unique (type, scalar)
);

It is possible to insert a single value:

insert into value1 values
  (11, 1, 'x', 'just one');

But it is not possible to add another one:

insert into value1 values
  (12, 1, 'x', 'another one'); -- ERROR

But if scalar is NULL, it is possible to have more than one:

insert into value1 values
  (21, 2, null, 'first'),
  (22, 2, null, 'second');

My problem: it is possible to reference types, which do not exist.

This works although it should not:

insert into value1 values
  (12, 1, null, 'another');

It is allowed to use (1, NULL) as a type, although in the type table are just (1, 'x') and (2, NULL).

I tried to use match full on the foreign key, but this seems to do something different. It prevents also my intended use of multiple null values, because it rejects multiple null values at all.

I want to allow multiple NULLs in value1.scalar if type1.scalar is NULL. But I want to reject any NULL in value1.scalar if type1.scalar is not NULL. How can I express this?

3
  • unique (type, scalar) on the value1 table is wrong, it needs a WHERE to filter only to scalars. MATCH PARTIAL is what you want, but not implemented yet. Maybe use a flag value to signal a list? Dec 12, 2023 at 12:56
  • 1
    From my understanding, you’re using a Sum Type. I suggest reading typeable.io/blog/2019-11-21-sql-sum-types for other options. What you’re doing seems too complicated to me. Dec 12, 2023 at 13:29
  • @KevinMeredith Yes this was way too spooky. I now use a range and a before insert trigger. It is much easier to understand.
    – ceving
    Dec 12, 2023 at 16:04

1 Answer 1

2

You have two separate issues:

  • The unique constraint on value1 is enforcing a single row per set of (type, scalar), but you want non-scalars to allow multiples. Unique constraints do not allow filters.
    You can use a filtered unique index where (scalar is not null) instead, however.....
  • ... the other issue is that both MATCH SIMPLE and MATCH FULL don't do what you want. You need MATCH PARTIAL, but this has not been implemented yet.

Instead, use flag value such as \x0 (the NUL character) to signal a non-scalar, and make the scalar column non-nullable on type1.

create table type1 (
  id     integer primary key,
  scalar "char" not null,
  name   text,
  unique (id, scalar)
);

create table value1 (
  id     integer primary key,
  type   integer,
  scalar "char",
  value  text,
  foreign key (type, scalar) references type1 (id, scalar) match full  -- or match simple (the default) if you prefer
);

create unique index ux on value1 (type, scalar) where (scalar <> '\x0');

db<>fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.