0

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.)

2 Answers 2

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'));
3
  • 1
    "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. Oct 15, 2021 at 9:19
  • 1
    @Leehan: it sounds as if that "list" should be a separate table. Then use a foreign key reference from mytable to that "list table"
    – user1822
    Oct 15, 2021 at 10:16
  • Or maybe it could be split using generated columns?
    – Vérace
    Oct 15, 2021 at 11:24
0

Similar to what ypercubeᵀᴹ 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.