0

I'm adding constraint on an existing table 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 = '{}' ));

What I want is

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

I know this error_message != '{}' OR error_message IS NOT NULL is not written properly. But I'm stuck finding a way to write it.

1

The constraint should be written using an OR like (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
-1

Why are you doing

CHECK ( NOT (failed IS FALSE AND (error_message != '{}' OR error_message IS NOT NULL)) );

And not

CHECK (
  failed IS FALSE
  AND (error_message = '{}' OR error_message IS NULL )
);

Or, even better rather than keeping state across multiple booleans like failed and error_message use a single ENUM.

CREATE TYPE terminal_job_state AS ENUM ( 'FAILED', 'COMPLETED' )
2
  • Thanks but with your answer INSERT INTO jobs VALUES (false, true, null); raise and error violates check constraint "error_message_is_null_if_failed_is_false". – Mio Oct 25 '17 at 22:08
  • But good idea for the enum. Didn't think about it – Mio Oct 26 '17 at 6:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.