Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


-- setup date_types and unknown_values 
create table date_types ( 
    date_type            text  primary key  ,
    definition           text     
 );

create table unknown_values ( 
    code                 text  primary key  ,
    definition           text     
 );

insert into date_types (date_type, definition)
values
    ('type_a', 'The first date type'),
    ('type_b', 'The second date type');

insert into unknown_values (code, definition)
values
    (':unac', 'Temporarily inaccessible'),
    (':unal', 'Unallowed, suppressed intentionally'),
    (':unap', 'Not applicable, makes no sense'),
    (':unas', 'Value unassigned (e.g., Untitled)'),
    (':unav', 'Value unavailable, possibly unknown'),
    (':unkn', 'Known to be unknown (e.g., Anonymous, Inconnue)'),
    (':none', 'Never had a value, never will'),
    (':null', 'Explicitly and meaningfully empty'),
    (':tba', 'To be assigned or announced later'),
    (':etal', 'Too numerous to list (et alia)');
2 rows affected

10 rows affected

-- as originally defined (changes to Postgres syntax) 
create table dates ( 
    date_id              integer generated always as identity  primary key, 
    date_value           date not null default current_date   ,
    date_type            text not null    ,
    foreign key ( date_type ) references date_types( date_type )  
 );
alter table dates 
      alter column date_type 
            drop not null; 
           
alter table dates 
      add  unknown_value text 
           references unknown_values(code); 
          
alter table dates 
      add constraint  one_null 
          check (num_nulls(date_type, unknown_value ) = 1);
      
insert into dates (date_value, date_type)  values ( date '2021-09-15', 'type_a') ;
insert into dates (date_value, unknown_value)  values ( date '2021-10-12', ':none') ;
select * from dates; 
1 rows affected

1 rows affected



date_id
date_value
date_type
unknown_value


1
2021-09-15
type_a
null

2
2021-10-12
null
:none


 -- try both type and unknown
 insert into dates (date_value, date_type, unknown_value) 
      values ( date '2021-10-12','type_b',':tba') ;
ERROR:  new row for relation "dates" violates check constraint "one_null"
DETAIL:  Failing row contains (3, 2021-10-12, type_b, :tba).


  -- try to omit both type and unknown
 insert into dates (date_value) values ( date '2021-11-22'); 
ERROR:  new row for relation "dates" violates check constraint "one_null"
DETAIL:  Failing row contains (4, 2021-11-22, null, null).