Postgres SQL: How to create a constraint to check an element is not in an array of string

user_4239402 0
Viewed 505

I'm learning about constraints, and I haven't found the way to check if a value is in an array.

Here s my problem:

City: text
Other_cities: array of text

I'd like to say that city must be different of other cities. I'd like to check other cities are all different

ALTER TABLE address 
            ADD CONSTRAINT  "check cities are different"
            CHECK (
            City <> Other_cities
            );

I guess this code is wrong as it's not checking every other cities one by one.

How can I do?

constraints postgresql arrays
2 Answers

Use any to check if an element is in an array.

test=> select not 'Moose Factory' = any( array['Moose Factory', 'Walla Walla'] );
 ?column? 
----------
 f
(1 row)
user_146600

You could check that city is different from all values in other_cities:

ALTER TABLE address ADD CHECK ((city <> ALL (other_cities)) IS TRUE);

The IS TRUE will take care of NULL values in the array. Without it, the constraint would pass arrays that contain a NULL value, because check constraints allow rows where the test evaluates to NULL.

user_64643080
Related