[] Dark Mode

Common Postgresql Mistakes

(posted in blog)

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.

The Multiple Meanings of STRICT

The STRICT keyword has different behaviors depending on where you’re using it.

1
2
3
4
SELECT my_column
INTO STRICT my_variable
FROM my_table
WHERE id=123;

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.

1
2
3
4
5
6
7
8
9
DEFINE FUNCTION my_function(
my_parameter1 int,
my_parameter2 text
) RETURNS int
LANGUAGE sql STRICT
AS $$
-- do something useful
select null;
$$;

The above function is declared with the STRICT keyword. Based on the meaning of STRICT using 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.

Array ANY Versus ALL in WHERE Clauses

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:

1
2
SELECT * FROM my_table
WHERE id = ANY(my_id_array);

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:

1
2
3
-- this is wrong
SELECT * FROM my_table
WHERE id != ANY(my_id_array);

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:

1
2
WHERE 123 != ANY(456, 789) -- returns false
WHERE 123 != ANY(123, 456) -- returns false because 123 != 456

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 NOT:

1
2
3
4
5
6
7
-- this is correct
SELECT * FROM my_table
WHERE id != ALL(my_id_array);

-- this is also correct
SELECT * FROM my_table
WHERE NOT (id = ANY(my_id_array));

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

Comments