Postgres Contraint One of the columns NOT NULL

While creating some tables for an application, I have the requirement that in a table at least one of two columns should be NOT Null. This can be checked in the application, but it is better also to do it in the database level in case where we have problems in the application and to make sure that we respect all the time this constraint.

To do so, we can add this constraint in Postgres :


CONSTRAINT chk_onenotnull CHECK (NOT ROW(mobile_phone, home_phone) IS NULL) 

That's all :) .

Fix UNIQUE CONSTRAINT with null value

In my database, I have UNIQUE constraint with two columns col_1 and col2.
But, I have detected that while inserting in the database two records with the same value in col_1 and null in col_2, postgres accept it and take null values as different.

So as solution, I have added a default value. My col_2 was a date, so I have inserted a future date '2040-01-01' and in the request, I just do :

select from table where col_2 >= now();

Using JPA, I also disable it to insert a NULL value and let the database putting the default value by using insertable =  flase. Please see this link for more details.