PostgresQL: Value is only allowed in one of two columns

Question

I have a database with two tables "Config" and "Config_xml", each consisting of the same columns (id, content, modifier, etc...). The only difference is, that config only contains non-xml strings in its content column, whereas config_xml contains an xml string in its content column.

Now I'd like to combine these two tables into one, providing a content column and an xml_content column, to simplify querying, because at the moment I always have to query on both tables.

Now is there a way to constrain each row to allow a value in either content or xml_content?
Thanks in advance.

Answer 1

You can use a check constraint that requires one column to be null.

alter table the_table
   add constraint check_content
   check (num_nulls(config, config_xml) = 1);

To also avoid empty strings, you might want to use:

   check (num_nulls(nullif(trim(config::text), ''), nullif(trim(config_xml::text), '')) = 1)