Hi
Not an important question, but a niggle.
CREATE TABLE gwtest (id INT PRIMARY KEY); INSERT INTO gwtest VALUES (1),(2),(3);
SELECT COALESCE((SELECT 'Yes' FROM gwtest WHERE id=4), 'No') AS valid;
gives an error
failed to find conversion function from unknown to text
I can work around this with
SELECT COALESCE((SELECT 'Yes' FROM gwtest WHERE id=4)::varchar, 'No') AS valid;
I'm guessing this is because Postgres can't deduce the type of the
string column from the source when the result isn't returned. Oddly,
it also seems to cope when I do:
SELECT COALESCE((SELECT 'Yes'::varchar FROM gwtest WHERE id=4), 'No') AS valid
which _really_ blows my mind.
I'm aware I could use EXISTS instead (and it would be much nicer) and
(as above) I can fix it with an explicit cast, but it seems odd that
a) COALESCE can infer the type from the string when one is returned
and from an explicitly cast string that _isn't_ returned, but can't
infer the type from the non-cast version, and b) it needs a type for
NULL at all (since any NULL is going to be treated the same).
(running 9.5, if it matters)
Am I missing something?
Geoff