Today I Learned

A Hashrocket project

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.

Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.