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

Question

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?

Since "non-nullable" is a tecnical term with a meaning already, don't use it for anything else, like whatever it is you mean. — philipxy, Jun 03 '20 at 06:17

score 4 · Answer 1 · answered Jun 03 '20 at 06:01

4

You can use check with array_position() as following

CREATE TABLE foo (
  things text[] NOT NULL check (array_position(things, null) is null)
);

and you can also check for empty array

CREATE TABLE foo (
  things text[] NOT NULL check (things <> '{}' and array_position(things, null) is null)
);

answered Jun 03 '20 at 06:01

zealous

7,336
4
16
36

That's beautiful, and you both guys had the same idea instantly! – Andronicus Jun 03 '20 at 06:09

score 3 · Accepted Answer · answered Jun 03 '20 at 06:02

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)
);

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

2 Answers2