Why in PostgreSQL enum type array null values allowed?

Question

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.

user330315 · Accepted Answer · 2020-09-17 07:17:31Z

4

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

answered Sep 17, 2020 at 7:17

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Matt Whitlock Jan 27 at 7:52

It will be faster and use less RAM to define the check as CHECK (array_position(my_column, NULL) IS NULL). Then you're not allocating a new array in memory and populating it with values just to perform a check.

Hasan Kaya · Accepted Answer · 2020-09-17 07:00:15Z

-2

another way

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

answered Sep 17, 2020 at 7:00

Hasan Kaya

1

Collectives™ on Stack Overflow

Why in PostgreSQL enum type array null values allowed?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related