Postgresql tuple constraints about NOT NULL

I am building a database in POSTGRESQL, and I would like to create NOT NULL constraints for my columns, where one and only one column would be NOT NULL.

I have two columns in my table, site_id and buffer_result_id. Only one of these columns will have values.

alter table dt.analysis_result
add constraint ar_check check (site_id NOT NULL OR buffer_result_id NOT NULL);

The above code is just some pseudo-code to show my idea. How can I achieve this function?

Answer

You could use XOR expressed as:

alter table dt.analysis_result
add constraint ar_check check (
 (site_id IS NOT NULL OR buffer_result_id IS NOT NULL) 
  AND NOT(site_id IS NOT NULL AND buffer_result_id IS NOT NULL) 
 );

db<>fiddle demo

More info: Exclusive OR – Equivalences

---

Demo:

CREATE TABLE analysis_result(site_id INT, buffer_result_id INT);

INSERT INTO analysis_result VALUES (NULL, NULL);
-- ERROR:  new row for relation "analysis_result" violates check constraint "ar_check"

INSERT INTO analysis_result VALUES (1, 2);
-- ERROR:  new row for relation "analysis_result" violates check constraint "ar_check"

INSERT INTO analysis_result VALUES (NULL, 2);
INSERT INTO analysis_result VALUES (1, NULL);

SELECT * FROM analysis_result