Re: BUG #5490: INSERT doesn't force cast from text to timestamp

On Jun 7, 2010, at 7:53 AM, Tom Lane wrote:

> Andy Balholm <andy(at)balholm(dot)com> writes:
>> Is there a way to make values of "undefined" type pass through the
>> SELECT DISTINCT filter (getting checked for uniqueness) but remain
>> "undefined"
>
> No. What is your criterion for deciding that two values are distinct?
> It's not possible to do that without imputing a data type to them.
> (In particular, comparing the character strings amounts to deciding
> that they are of a textual data type --- failing to acknowledge that
> isn't a workaround but merely sloppy thinking.)
>
> regards, tom lane
>

I see your point about the fuzziness of deciding what constitutes a distinct value before the type is determined. My proposal was so general that it would still open a can of worms.

In Farid's particular use case, it's easy to see that the values aren't distinct, because they're all textually identical. In that case, SELECT DISTINCT could simply ignore that column while deciding which rows are distinct, and then tack it back on when it returns its result. That would be a special-case hack, but I suspect that it would actually cover most cases where undefined types are used in SELECT DISTINCT, since undefined types come from literals in the SQL, which would generally be the same for all rows. Data that varies by row would usually come from real tables, where types are already defined.