Re: [GENERAL] COALESCE requires NULL from scalar subquery has a type

2016-02-09 Thread Rowan Collins
David G. Johnston wrote on 08/02/2016 16:05: On Mon, Feb 8, 2016 at 8:25 AM, Geoff Winkless >wrote: On 8 February 2016 at 14:49, Tom Lane mailto:t...@sss.pgh.pa.us>> wrote: > Yup. The output column type of the sub-SELECT is determined without > reference

Re: [GENERAL] COALESCE requires NULL from scalar subquery has a type

2016-02-09 Thread Geoff Winkless
On 9 February 2016 at 15:16, David G. Johnston wrote: > Same error...I tested using the table as well...also the same error for > values of id between 1 and 3. Oh my. In my memory, this was working. I try it now, and it doesn't. Apologies: I've obviously managed to lose track of what worked and

Re: [GENERAL] COALESCE requires NULL from scalar subquery has a type

2016-02-09 Thread David G. Johnston
On Tue, Feb 9, 2016 at 7:56 AM, Geoff Winkless wrote: > On 9 February 2016 at 14:53, Tom Lane wrote: > > SELECT COALESCE((SELECT 'Yes' FROM gwtest WHERE id=4), 'No') AS valid; > > > > There's no null visible anywhere in that. I suppose that if there's > > no row with id=4, there would be a null

Re: [GENERAL] COALESCE requires NULL from scalar subquery has a type

2016-02-09 Thread Geoff Winkless
On 9 February 2016 at 14:53, Tom Lane wrote: > SELECT COALESCE((SELECT 'Yes' FROM gwtest WHERE id=4), 'No') AS valid; > > There's no null visible anywhere in that. I suppose that if there's > no row with id=4, there would be a null at runtime, Well yes, that was the whole point. > but that's no

Re: [GENERAL] COALESCE requires NULL from scalar subquery has a type

2016-02-09 Thread Tom Lane
Geoff Winkless writes: > I'm not asking that it coerce an actual value with a genuinely unknown > type to a text value: I'm simply suggesting that it's unnecessary for > COALESCE to coerce an unknown-typed NULL into anything (even if you > ignore that NULL is, as far as I know, equivalent, no matt

Re: [GENERAL] COALESCE requires NULL from scalar subquery has a type

2016-02-09 Thread Merlin Moncure
On Tue, Feb 9, 2016 at 5:42 AM, Geoff Winkless wrote: > On 8 February 2016 at 16:05, David G. Johnston > wrote: >> While explicit casting of literals can at times be annoying and seemingly >> unncessary I wouldn't call it unintuitive. > > Well that very much depends on your definition of intu

Re: [GENERAL] COALESCE requires NULL from scalar subquery has a type

2016-02-09 Thread Geoff Winkless
On 8 February 2016 at 16:05, David G. Johnston wrote: > While explicit casting of literals can at times be annoying and seemingly > unncessary I wouldn't call it unintuitive. Well that very much depends on your definition of intuitive. If something is "seemingly unnecessary" I would say that'

Re: [GENERAL] COALESCE requires NULL from scalar subquery has a type

2016-02-08 Thread David G. Johnston
On Mon, Feb 8, 2016 at 8:25 AM, Geoff Winkless wrote: > On 8 February 2016 at 14:49, Tom Lane wrote: > > Yup. The output column type of the sub-SELECT is determined without > > reference to its context, so there's nothing causing the unknown-type > > literal to get assigned a definite type. > >

Re: [GENERAL] COALESCE requires NULL from scalar subquery has a type

2016-02-08 Thread Geoff Winkless
On 8 February 2016 at 14:49, Tom Lane wrote: > Yup. The output column type of the sub-SELECT is determined without > reference to its context, so there's nothing causing the unknown-type > literal to get assigned a definite type. Mm. I can follow that, although it makes me unhappy that casting t

Re: [GENERAL] COALESCE requires NULL from scalar subquery has a type

2016-02-08 Thread Tom Lane
Geoff Winkless writes: > SELECT COALESCE((SELECT 'Yes' FROM gwtest WHERE id=4), 'No') AS valid; > gives an error > 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