Re: DISTINCT with NULLs and INT fields

"George Pavlov" <gpavlov(at)mynewplace(dot)com> writes:
> the interesting thing here is that 4::int gets into a text
> field whereas 4::text does not get into an integer field. seems to me
> like there is an implicit int-to-text cast (without a symmetrical
> text-to-int one)

Yeah, there is. You can easily see the list of implicit casts for
yourself:

select castsource::regtype, casttarget::regtype from pg_cast
where castcontext = 'i' and castsource != casttarget;

There's 90 of them in current CVS tip, and most of 'em are unsurprising
within-type-category casts, such as implicit promotion of int to bigint.
However we have about a dozen implicit casts to text from the numeric
and datetime type categories. Personally I would dearly love to get
rid of these, because they are accidents waiting to happen (and they
do regularly bite people, see the mail list archives for evidence).
But it seems people expect to be able to do things like
number || ' string'
without explicitly casting the number to text.

regards, tom lane