Re: When is an explicit cast necessary?

Alan Millington wrote:
>
> However, every language that I have ever used will implicitly convert
> an integer 1 to a smallint (or short) 1 when required to do so. How
> can such a cast be called "surprising behaviour", or produce
> "misleading results", to quote the first article?
>

SELECT ' 1'=(' 1'::numeric)::text;

That returns false, and is typical of the sort of surprising behavior
you can see if you just allow sloppy casts everywhere.  Casting between
numeric and text types is not a lossless operation.  Here's another one:

SELECT '01'=('01'::numeric)::text;

Also false.  This variation has made my life difficult more than once
when inventory part numbers at a company were allowed to start with a
leading 0, and subqueries (such as you'll see when using a view) were
involved.  You can have two queries that each work fine on their own,
but chain them together by making one run against a subquery of the
other and you can get mysteriously burned when things aren't equal the
way you expected.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us