Using PostgreSQL 9.6 I can create a column with type 'not-null-array of string' with:
CREATE TABLE example (
foo TEXT[] NOT NULL
);
but this allows the elements to be null, i.e I can do:
INSERT INTO example VALUES('{NULL}')
Is there a way to instead create a column with type 'not-null-array of not-null-string'? I'd like something like this:
CREATE TABLE example (
foo (NOT NULL TEXT)[] NOT NULL
);
but it's not syntactically valid. Is there a valid way to express this?
Simpler since pg 9.5, with the addition of array_position()
CREATE TABLE example (
foo TEXT[] NOT NULL check (array_position(foo, null) is null)
);
You might also want to check for an empty array:
CREATE TABLE example (
foo TEXT[] NOT NULL check (foo <> '{}' and array_position(foo, null) is null)
);
Use the function:
create or replace function are_elements_not_null(arr anyarray)
returns boolean language sql immutable
as $$
select bool_and(elem is not null)
from unnest(arr) as elem
$$;
in a check constraint:
create table example(
foo text[] check(are_elements_not_null(foo))
);
insert into example values
(array[null]);
ERROR: new row for relation "example" violates check constraint "example_foo_check"
DETAIL: Failing row contains ({NULL}).
Note that the column still may be null or may contain an empty array. If you want to exclude these cases expand the definition in the way:
create table example(
foo text[] check(are_elements_not_null(foo) and foo <> '{}') not null
);
