添加數組表約束，如果其他列為 false，則為空或 null

我在現有表上添加約束jobs：

DROP TABLE IF EXISTS jobs CASCADE;
DROP TYPE IF EXISTS job_state;
CREATE TYPE job_state AS ENUM ( 'UNSTARTED', 'STARTED', 'FAILED', 'COMPLETED' );
CREATE TABLE jobs(
status job_state NOT NULL DEFAULT 'UNSTARTED',
error_message text[]
);

ALTER TABLE jobs
ADD CONSTRAINT error_message_is_null_if_failed_is_false 
  CHECK ( status IN ('UNSTARTED', 'STARTED', 'COMPLETED') AND ( error_message IS NULL OR error_message = '{}' ));

我想要的是

INSERT INTO jobs VALUES ('UNSTARTED', null);
INSERT INTO jobs VALUES ('STARTED', null);
INSERT INTO jobs VALUES ('FAILED', null); -- I would like to be able to do this

我知道這error_message != '{}' OR error_message IS NOT NULL寫得不對。但我一直在尋找一種寫它的方法。

約束應該使用ORlike來編寫(NOT A) OR B

ALTER TABLE jobs
ADD CONSTRAINT error_message_is_null_if_failed_is_false 
  CHECK ( NOT ( status IN ('UNSTARTED', 'STARTED', 'COMPLETED')) OR ( error_message IS NULL OR error_message = '{}' ));

INSERT INTO jobs VALUES ('UNSTARTED', null); -- no error
INSERT INTO jobs VALUES ('STARTED', null); -- no error
INSERT INTO jobs VALUES ('FAILED', null); -- no error
INSERT INTO jobs VALUES ('COMPLETED', null); -- no error

INSERT INTO jobs VALUES ('UNSTARTED', '{}'); -- no error
INSERT INTO jobs VALUES ('STARTED', '{}'); -- no error
INSERT INTO jobs VALUES ('FAILED', '{}'); -- no error
INSERT INTO jobs VALUES ('COMPLETED', '{}'); -- no error

INSERT INTO jobs VALUES ('UNSTARTED', '{"error message"}'); -- error
INSERT INTO jobs VALUES ('COMPLETED', '{"error message"}'); -- error
INSERT INTO jobs VALUES ('STARTED', '{"error message"}'); -- error
INSERT INTO jobs VALUES ('FAILED', '{"error message"}'); -- no error

引用自：https://dba.stackexchange.com/questions/189296