Re: DISTINCT with NULLs and INT fields

> > test=# insert into foo values (4::int,4::int);
> > INSERT 0 1

> > test=# insert into foo values (4::text,4::text);
> > ERROR: column "b" is of type integer but expression is of type text
> > HINT: You will need to rewrite or cast the expression.

> > test=# insert into foo values (cast(4 as int),cast(4 as int));
> > INSERT 0 1

> > test=# insert into foo values (cast(4 as text),cast(4 as text));
> > ERROR: column "b" is of type integer but expression is of type text
> > HINT: You will need to rewrite or cast the expression.
>
> The last sentence (and the second one as well -- they are exactly
> equivalent) exemplifies that there isn't an implicit typecase
> from text
> to integer. If you use single quotes instead of an explicit cast, the
> literal is assumed to be of type "unknown", which _can_ be casted
> automatically to integer.

yes, #1 is the same as #3, and #2 is the same as #4. i was trying to
provide a counter example to tom's statement that pgsql does not do type
coercions. 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) unless 4::int is somehow *still* considered an 'untyped
literal' (4 and '4' seem untyped to me, but 4::int does not)??