How to limit the length of an array in PostgreSQL?

Question

Is there any way to add a constraint on a column that is an array to limit it's length? I want these arrays to be no longer than 6. And yes, I understand that often a new table is better than storing in an array but I am in a situation where an array makes more sense.

Answer 1

You can add a CHECK constraint to the table definition:

CREATE TABLE my_table (
    id  serial PRIMARY KEY,
    arr int[] CHECK (array_length(arr, 1) < 7),
    ...
);

If the table already exists, you can add the constraint with ALTER TABLE:

ALTER TABLE my_table ADD CONSTRAINT arr_len CHECK (array_length(arr, 1) < 7);