CODEWITHSUNDEEP
sqlarrayspostgresqldatabase-designsql-null
Tobias Hermann
Tobias Hermann

Reputation: 10966

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?

Upvotes: 5

Views: 1025

Answers (2)

user330315
user330315

Reputation:

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

Upvotes: 4

zealous
zealous

Reputation: 7503

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

Upvotes: 5

Related Questions