Postgres `coalesce` errors with multiple types
The Postgres function coalesce
takes a variable number of arguments and returns the first non-null argument.
psql> select coalesce(null, null, null, null, 'hi!');
hi!
But if you include a number and a string, then Postgres throws an error.
psql> select coalesce(1, 'hi!');
ERROR: invalid input syntax for integer: "hi!"
Coalesce will try to coerce value to the type of the first argument if possible.
psql> select coalesce(1, '2');
1
Mysql behaves a bit differently, it allows values of different types.
mysql> select coalesce(1, 'hi!');
1
The postgres way makes a bit more sense to me. What is the type of a column if there are multiple possible types? Is it always a string? But I wanted a number? I feel like the additional type safety will help catch errors a bit earlier.
Tweet