Re: BUG #16510: Count Distinct with non distinct column in combination with string constants throws error

Daniel Gustafsson <daniel@yesql.se> writes:
> PostgreSQL doesn't know which datatype you expect 'blub' to be, as it isn't
> related to the testtable relation in your query.  If you cast to the datatype
> of your choice you will get the expected result.

> postgres=# SELECT COUNT(DISTINCT(testtable.column2, 'blub')) FROM public.testtable;
> ERROR:  could not identify a comparison function for type unknown

The reason for this might be a little more obvious if you wrote the
implicit row constructor explicitly, ie

SELECT COUNT(DISTINCT ROW(testtable.column2, 'blub')) FROM public.testtable;

The row's datatype is indeterminate as-specified.

Perhaps there's room to argue that we should allow 'unknown' to decay to
'text' automatically in this context, but I'm not in a big hurry to do
that.  It seems better to make people be explicit about which datatype
they intend inside such complex, infrequently-used constructs.

            regards, tom lane