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