Re: Smallint needs explicit cast in psql?

Matt Musgrove <MMusgrove(at)efji(dot)com> writes:
> It still doesn't make sense to the two of us here at work. Perhaps if
> I show you a slightly expanded example you'll understand why we are so
> confused. We have a table with a smallint column and we can do inserts
> without problems.

Right, because assigning to a table column is an "assignment" cast ---
which has more liberal rules, because either you can coerce the value
to the table column's type or you're going to fail anyway. So the
system will take the integer literal and try to downcast to smallint,
and if that doesn't work then the query was doomed anyhow.

Coercion of a value to match a function argument is a different matter;
the system will not choose a coercion that is "surprising" in any way
or has the potential to fail due to issues such as range limitations.
The reason for the difference in behavior is probably not very obvious
when you have only one possible function to match, but we need this
restriction in order for things to behave sanely with overloaded
functions (or operators). The text in chapter 10 expands on that a
bit.

regards, tom lane