In SQL, how do I prevent array elements from being null?

I’m designing a SQL database and I’d like to make sure my schema is as strict as possible. Is there a way to require that an array column doesn’t contain null elements? As I understand it, I can make the whole column NOT NULL, but that’s not the same thing since an array of null elements isn’t itself null.

Do I need to use a trigger or a CHECK constraint for this, or is there a more straightforward (and efficient) way?

Here’s a minimal example using PostgreSQL:

CREATE TABLE foo (things INTEGER ARRAY NOT NULL);
INSERT INTO foo(things) VALUES ('{9, NULL}');
SELECT * FROM foo;

…which gives this result:

  things  
----------
 {9,NULL}
(1 row)

…whereas I want it to prevent the insertion because of the null array element.

Answer

You can use a check constraint:

check (array_position(things, null) is null)

Advertisement

Answer