postgres check constraint on length of elements in text[]

Question

Is there a possibility to add a check constraint to ensure the length of the elements in a text[] in postgres?

I want each element to have a length of 2 characters (country iso code).

Usecase: it's a filter. The entity saved in that row should only be active in the countries included in that array.

Bergi · Accepted Answer · 2022-11-29 00:45:15Z

3

It's possible, but ugly since you'd need a 2 = ALL (SELECT length(value) FROM unnest(arr)) subquery which you'd have to place in an immutable helper function like here.

Instead, why not just switch from an array of text to the appropriate character type: character(2)[]?

answered Nov 29, 2022 at 0:45

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

3 Comments

andymel Over a year ago

Thanks a lot, especially for the character(2) hint!

andymel Over a year ago

Hmm, when an element is longer, it's fine (error), but when the element is shorter it is silently filled with whitespace characters. Any idea how to recognize too short elements?

Bergi Over a year ago

oh right, I forgot about the space-padding, that's annoying here. I guess you should either go with a varchar(2) and use the length restriction, or you use char(2) and add an extra check constraint that checks for valid characters (e.g. array_to_string(arr, '') ~ '^[a-z]+$')

Collectives™ on Stack Overflow

postgres check constraint on length of elements in text[]

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related