array intersection in postgres

Recently, I was building an API to modify the data in a database.

The field I was modifying happened to be an array, and I wanted to make sure that what the client was suggesting be added to the database was a valid value.

Each row happened to a pre-defined set of “available” classes — classes that could be enabled or disabled. For example:

This was good news: I had the data readily available. The question was how do I make use of it? What I wanted to do was throw an error if the client tried to enable a class of d on any of the rows or even a class c on row ID 4.

Fortunately, PostgreSQL has a concept of array intersection built into the API for Array Operators¹.

The constraint formulation used the “contains” syntax (@>), though reversing the order I could easily have used the “is contained by” (<@).

To add the constraint, I added the CONSTRAINT to the DDL file for the table using the array operator.²

CREATE TABLE IF NOT EXISTS my_table (
    id text
    ...
    , classes_available text[]
    , classes_enabled text[]

    -- Constraint
    CONSTRAINT allowed_classes CHECK (classes_available @> classes_enabled)
);

Now, if a client were to try to send a value that wasn’t contained by the classes_available, postgres would throw an error that would be sent back via the API call. This is great because I don’t need to manage it separately.

Footnotes

• ¹ Array Functions and Operators | PostgreSQL
• ² Data definition language | Wikipedia