Why in PostgreSQL enum type array null values allowed?

user_4099983 0
Viewed 422

I am working with a PostgreSQL DB, and use an array of an enum type as one of the columns.

CREATE TYPE my_type_enum AS ENUM ('value1', 'value2', 'value3');
ALTER TABLE "my_table" ADD COLUMN IF NOT EXISTS "my_column" my_type_enum ARRAY;

The enum gives me to add only valid values that it contains, but I also can add a null elements to it.

So, to prevent saving null elements to DB, I added a filter in the app, but I wonder if there is a definition in PostgreSQL not to allow adding null elements to array?

P.S. I saw here suggestion to add function as part of the column definition, but this is not what I asked.

postgresql
2 Answers

You can add a check constraint to prevent elements with NULL values.

alter table my_table
  add constraint no_null_element 
  check (cardinality(my_column) = cardinality(array_remove(my_column, null)));

A cleaner solution would be a normalized one-to-many relationship with a foreign key column defined as not null

user_3303150

another way

IF array_length(arrayname, 1) > 0 THEN transactions END IF;

user_142922720
Related