Non-null constraints using triggers in PostgreSQL

Question

I have a table with two optional columns. When a row is inserted in the table, one of the two columns should not null.

I found this solution for MySQL:

CREATE TABLE foo (
  FieldA INT,
  FieldB INT
);

DELIMITER //
CREATE TRIGGER InsertFieldABNotNull BEFORE INSERT ON foo
FOR EACH ROW BEGIN
  IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'FieldA\' and \'FieldB\' cannot both be null';
  END IF;
END//
CREATE TRIGGER UpdateFieldABNotNull BEFORE UPDATE ON foo
FOR EACH ROW BEGIN
  IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'FieldA\' and \'FieldB\' cannot both be null';
  END IF;
END//
DELIMITER ;

INSERT INTO foo (FieldA, FieldB) VALUES (NULL, 10); -- OK
INSERT INTO foo (FieldA, FieldB) VALUES (10, NULL); -- OK
INSERT INTO foo (FieldA, FieldB) VALUES (NULL, NULL); -- gives error
UPDATE foo SET FieldA = NULL; -- gives error

How can I translate it for PostgreSQL?

You only need this ugly workaround in MySQL because it doesn't support check constraints. In Postgres this is way easier. — user330315
– user330315, Commented Jun 22, 2016 at 9:48

Renzo · Accepted Answer · 2016-06-22 09:45:24Z

1

You could simply use a table constraint:

CREATE TABLE foo (
  FieldA INT,
  FieldB INT,
  CHECK (NOT (FieldA IS NULL AND FieldB IS NULL)));

In this way you cannot insert or modify a tuple with both values NULL.

teststar=# INSERT INTO foo (FieldA, FieldB) VALUES (NULL, 10);
INSERT 0 1
teststar=# INSERT INTO foo (FieldA, FieldB) VALUES (10, NULL);
INSERT 0 1
teststar=# INSERT INTO foo (FieldA, FieldB) VALUES (NULL, NULL);
ERROR:  new row for relation "foo" violates check constraint "foo_check"
DETAIL:  Failing row contains (null, null).
teststar=# UPDATE foo SET FieldA = NULL; 
ERROR:  new row for relation "foo" violates check constraint "foo_check"
DETAIL:  Failing row contains (null, null).
teststar=#

answered Jun 22, 2016 at 9:45

Renzo

27.6k5 gold badges54 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fabio Over a year ago

Very simple to understand and use!! Thank you!

Collectives™ on Stack Overflow

Non-null constraints using triggers in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related