BUG #1904: using distinct with null col causes error
The following bug has been logged online:
Bug reference: 1904
Logged by: Brian Cox
Email address: bcox@timestock.com
PostgreSQL version: 8.0.3
Operating system: Linux (Fedora Core 2)
Description: using distinct with null col causes error
Details:
any_db=> create view view1 as select null as col1 from any_table;
CREATE VIEW
any_db=> select distinct col1 from view1;
ERROR: failed to find conversion function from "unknown" to text
"Brian Cox" <bcox@timestock.com> writes:
any_db=> create view view1 as select null as col1 from any_table;
CREATE VIEW
You seem to have ignored the warning telling you this probably wasn't
a good idea:
regression=# create view view1 as select null as col1;
WARNING: column "col1" has type "unknown"
DETAIL: Proceeding with relation creation anyway.
CREATE VIEW
regression=#
According to the letter of the SQL standard, the above is illegal;
you're really supposed to CAST the null to some specific datatype.
If we were to change anything in response to this complaint, it'd
probably be to promote the WARNING to an ERROR.
regards, tom lane