constraint check values with regular expression

Question

In a table with a field code, I want to authorize the user to choose one value among a list.
Here is a sample of the values of the list :

A|AG|AL|AS|B|C|D|DA

I added a constraint on code like this :

ALTER TABLE mytable
ADD CONSTRAINT verif_code CHECK (code::text ~ '(A|AG|AL|AS|B|C|D|DA)')

Despite of this, I can add an extra value after a first one picked up in the list.

ALY accepted -> :(
Y rejected -> :)

How to avoid the possibility of adding a value in the second position that is not in the list?

(I could use a IN operator but at the end, the authorized values for code will be a combinaison of three sets of lists.)

Answer 1

The value matches the condition because it contains A.

Regular expressions are not only complicated to understand, they are also often expensive to process (there is a connection between these two). So if you can do without them, do it.

In this case, the check constraint should be

ALTER TABLE mytable
ADD CONSTRAINT verif_code CHECK (code IN ('A', 'AG', 'AL', 'AS', 'B', 'C', 'D', 'DA'));

• Laurenz Albe: "Each part will be taken from a list": then you cannot use that in a check constraint, because you need to know that list when you create the constraint. That cannot be a list that changes based on what is in the database or something else that is not immutable. Perhaps you can ask another question that describes your actual problem.
• user1822: @Leehan: it sounds as if that "list" should be a separate table. Then use a foreign key reference from mytable to that "list table"
• Vérace: Or maybe it could be split using generated columns?

Answer 2

Similar to what ypercubeTM suggested:

If I understand correctly, it could probably be solved with a complex regex, e.g. CHECK (code SIMILAR TO '(A|AG|AL|AS|B|C|D|DA)(1|2|3|4)(X|Y|Z)') for a code that consists of 3 parts, the first as described in the question, the second part a digit from 1 to 4 and the 3rd part of a character, either X, Y or Z.

This expression works as expected:

code SIMILAR TO ('(A|AG|AL|AS|B|C|D|DA|DL|DS|G|GA)(d|h|l|m)?(_lab|_g|_h|_o)?')

One element of first list followed or not by one element of second list followed or not by one element of third list.

ex: Ad ; AG_g ; DSh_lab.