CODEWITHSUNDEEP
sqlpostgresql
ashtneoi
ashtneoi

Reputation: 45

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.

Upvotes: 0

Views: 335

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

You can use a check constraint:

check (array_position(things, null) is null)

Upvotes: 1

Related Questions