0

Is there any way to add a constraint on a column that is an array to limit length text objects? I know that I can do this without constraint:

colA varchar(100)[] not null

I tried to do it in the following way:

alter table "tableA" ADD CONSTRAINT "colA_text_size"
    CHECK ((SELECT max(length(pc)) from unnest(colA) as pc) <= 100) NOT VALID;
alter table "tableA" VALIDATE CONSTRAINT colA_text_size;

But got error: cannot use subquery in check constraint (SQLSTATE 0A000)

PavelA
  • 1
  • 1
  • Does this answer your question? [Postgresql - change the size of a varchar column to lower length](https://stackoverflow.com/questions/7729287/postgresql-change-the-size-of-a-varchar-column-to-lower-length) – blurfus Oct 31 '22 at 14:19
  • @blurfus No, I just need a limit for the maximum length of an array text object – PavelA Oct 31 '22 at 14:24
  • 2
    Why do you need a constraint if you can do it without a constraint ? – Edouard Oct 31 '22 at 14:39
  • 1
    I guess you could work around the subquery problem with a custom `immutable` function – Bergi Oct 31 '22 at 15:18

1 Answers1

0

Try the following definition for your check constraint: (see demo, for demo I limit length to 25).

check (length(replace(array_to_string( text_array ,','), ',','')) <= 100)

What it does:

  1. First the function array_to_string( ... ) converts the array to a csv.
  2. The replace() function then removes the commas replacing them with the zero length string ''.
  3. The length() function gets number of remaining characters in the string.
  4. Finally that number is compared to the limit value (100) and the check constraint is either passed of failed.

References: array_to_string(), replace(), length()

Belayer
  • 13,578
  • 2
  • 11
  • 22
  • Why not just use `array_to_string(text_array, '')` instead of adding commas only to remove them? – Bergi Nov 01 '22 at 00:09
  • This doesn't appear to do what the OP wants. It counts the number of characters in all texts in the array and compares the *sum* to the limit. OP wants to check every array element individually: no single text may be longer than the limit. – Bergi Nov 01 '22 at 00:10
  • Cause first thoughts are not always the best thoughts. – Belayer Nov 01 '22 at 00:10