postgres constraints and arrays

I wrote in the past about using constraints in the context of array operators in Postgres. Today, however, I didn’t have a pre-defined array. So, when I tried to create rule that would limit my ability to insert values that were inappropriate, I leaned on the previous learnings and tried something like:

CHECK (related_date @> ARRAY['val1', 'val2', ...])

Where the val1, val2, etc. represented the hard coded values I was trying to check against.

That didn’t work, however, as Postgres threw an error: text[] Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Okay, I can take a hint, let’s try explicit type casts:

CHECK ( related_date = ANY(ARRAY['val', 'other']::text[]) )

Notice that I’m no longer using the @> operator, however.

Good news: This works! But, it feels verbose and clumsy. Fortunately, I kept digging and I found a much simpler solution.

Because what I’m really asking is to check whether the value for related_date is in an array, that’s the same as:

CHECK ( related_date in ('val', 'other') )

The same restriction functionality, but much much simpler.

The only thing left is to see if I can reference a list defined elsewhere. Time will tell.

The latter solution was inspired by a lot of digging around the internet and particularly a conversation on StackOverflow.¹

My favorite part about learning this was less about the specifics but the fact that it solves a problem elegantly by reframing how I’m thinking about it.

Footnotes

• ¹ Check if value exists in postgres array | StackOverflow