additional_metadata of time series make use of the controlled vocabulary
It is not possible to define a foreign key on a subfield of a jsonb entry.
But it seems to be possible to define check constraints on such fields (see: https://www.enterprisedb.com/blog/illustration-jsonb-capabilities-postgres-95).
Therefore user functions can be defined to make use of the controlled vocabulary.
This is an example to check for the right values within the absorption_cross_section:
CREATE OR REPLACE FUNCTION check_absorption_cross_section(val integer) RETURNS boolean
AS
$$
SELECT val = ANY(SELECT enum_val FROM cs_vocabulary);
$$
LANGUAGE SQL;
This function should now be used as a check constraint and should then avoid wrong values being ingested.
It is added to the database with the following command:
ALTER TABLE timeseries
ADD CONSTRAINT ts_additional_metadata_absorption_cross_section_fk_cs_vocabulary_enum_val
CHECK(check_absorption_cross_section((additional_metadata->>'absorption_cross_section')::integer));
Edited by Sabine Schröder