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', ...])
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.
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!