How write CHECK condition for every element in array in postgreSQL

Question

I don't know how work with loops and arrays in PostgreSQL but i want make table with array which only accepts the given values.
('Icelandic','English','Polish','Danish','Norwegian','Swedish','French','German','Spanish')
How write this condition in shorter and prettier form?

 CREATE TABLE Test(
        UUID UUID NOT NULL PRIMARY KEY ,
        Name varchar(40) NOT NULL UNIQUE , --
        Languages text[] NOT NULL CHECK (
        Languages[1] IN (
        'Icelandic','English','Polish','Danish','Norwegian',
         'Swedish','French','German','Spanish'
            )
        AND
            Languages[2] IN (
        'Icelandic','English','Polish','Danish','Norwegian',
         'Swedish','French','German','Spanish'
            )
        AND
            Languages[3] IN (
        'Icelandic','English','Polish','Danish','Norwegian',
         'Swedish','French','German','Spanish'
            )
        AND
            Languages[4] IN (
        'Icelandic','English','Polish','Danish','Norwegian',
         'Swedish','French','German','Spanish'
            )
        AND
            Languages[5] IN (
        'Icelandic','English','Polish','Danish','Norwegian',
         'Swedish','French','German','Spanish'
            )     AND
            Languages[5] IN (
        'Icelandic','English','Polish','Danish','Norwegian',
         'Swedish','French','German','Spanish'
            )
        AND
            Languages[6] IN (
        'Icelandic','English','Polish','Danish','Norwegian',
         'Swedish','French','German','Spanish'
            )
        AND
            Languages[7] IN (
        'Icelandic','English','Polish','Danish','Norwegian',
         'Swedish','French','German','Spanish'
            )     AND
            Languages[8] IN (
        'Icelandic','English','Polish','Danish','Norwegian',
         'Swedish','French','German','Spanish'
            )
        AND
            Languages[9] IN (
        'Icelandic','English','Polish','Danish','Norwegian',
         'Swedish','French','German','Spanish'
            ) )
    
    );

Tometzky · Accepted Answer · 2020-11-22 14:39:09Z

3

A better solution would be to use the @> or <@ operators as described here https://www.postgresql.org/docs/12/functions-array.html

...CHECK (languages <@ ARRAY['Icelandic','English','Polish','Danish','Norwegian','Swedish','French','German','Spanish'])

However I suggest you use the common two-or-three letter language codes, instead of the full names. It's just more useful during migrations.

edited Nov 22, 2020 at 14:39

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

answered Nov 22, 2020 at 14:31

coladict

5,1251 gold badge20 silver badges31 bronze badges

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

1 Comment

Packman Over a year ago

Thanks for sollution and hint about langues codes ;)

Collectives™ on Stack Overflow

How write CHECK condition for every element in array in postgreSQL

1 Answer 1

1 Comment

Hot Network Questions