Search code examples
sqlarrayspostgresqldatabase-designsql-null

How to declare the elements of an array non-nullable?

In the following simple table

CREATE TABLE foo (
  things VARCHAR ARRAY
);

It's possible to insert null as an element of things:

INSERT INTO foo VALUES ('{"hi", null, "ho"}');

But I'd like to not allow this.

Changing the definition to the following, however,

CREATE TABLE foo (
  things VARCHAR ARRAY NOT NULL
);

Only prevents this

INSERT INTO foo VALUES (null);

which is not what I want. (I still want to allow that.)

So how can I declare not the column, but the elements of the array column to be non-nullable?

Solution

  • You can use a check constraint:

    CREATE TABLE foo 
(
  things text[], 
  constraint check_things_not_null 
    check ( cardinality(things) = cardinality(array_remove(things, null)))
);

    alternatively you can use array_position()

    
CREATE TABLE foo 
(
  things text[], 
  constraint check_things_not_null 
    check ( array_position(things, null) is null)
);