/* Code-Comments */

Postgres Constraints And Arrays

July 12, 2019

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: ERROR: operator does not exist: text @> 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.1

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


Stephen Weiss

Thanks for reading! My name's Stephen Weiss. I live in Chicago with my wife, Kate, and dog, Finn.
Click here to see the archives of my weeks in review and sign up yourself!