COALESCE requires NULL from scalar subquery has a type

From: Geoff Winkless <pgsqladmin(at)geoff(dot)dj>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: COALESCE requires NULL from scalar subquery has a type
Date: 2016-02-08 14:42:57
Message-ID: CAEzk6fdQvv8TmJzEUQ9aZ5_gxHSXF4jWWNYxn_fbrTcP6TBh9g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thom Brown 2016-02-08 14:43:09 Very slow DELETEs with foreign keys
Previous Message Geoff Winkless 2016-02-08 14:39:43 COALESCE requires NULL from scalar subquery has a type