Common Postgresql Mistakes
I’m not sure how common these mistakes are in general, but here are a couple gotchas that I’ve run into on more than one occasion.
STRICT keyword has different behaviors depending on where you’re using it.
In the above example where you’re using
STRICT in a
SELECT INTO statement, it will cause that statement to raise an exception if anything other than exactly one row matches. So if there are more than one record in
my_table that have
id=123 it would raise an exception, and if there are no rows that have
id=123 it would raise an exception. Without the
STRICT keyword this statement would return the first matching row or null in each of those cases respectively without raising an exception.
The above function is declared with the
STRICT keyword. Based on the meaning of
SELECT INTO, I always make the mistake of thinking that a
STRICT function must return exactly one result, and that if you end on a
SELECT statement that tries to return more than one row, or returns null, then the function will raise an exception.
That is not what
STRICT means on a function, however. When a function is declared as
STRICT it means that all the parameters to that function must be non-null. If a
STRICT function is called and any of the provided parameters are null, then the function will immediately return null. No exceptions will be raised and none of the function’s body will be executed.
When you want to match rows in which some column value is contained in an array of possible matches, you can use
ANY like this:
If you’re like me, you might think it logically follows that if you want to match rows in which your column value is not contained in an array of possible matches, you can simply flip the equality check:
What the above statement is actually matching is the case where
my_table.id is not equal to any value in the array. In other words, if
my_id_array contains more than one distinct value then the above statement could never return any rows:
The correct way to query what you want is to either flip the equality check and also use
ALL instead of
ANY, or leave the equality check alone and wrap your whole clause in a
In the above example you will return all rows except for the ones where
id is contained in
my_id_array, which is probably what you wanted.
Short Permalink for Attribution: rdsm.ca/1738b