Well I partially take back my last question. In the error message, I
missed the non-array / array part of "integer = text[]"
But I'm still confused. My subselect returns an array. If I cast it to a
text array, ANY is happy. But if I don't do so, what exactly does Postgres
think my subquery has yielded? And the error message still doesn't seem to
make sense...
On Mon, Sep 16, 2013 at 6:59 PM, Ken Tanzer <ken.tan...@gmail.com> wrote:
> OK I tried that and see it works with the cast. But now I'm confused
> about both what exactly is failing without the cast, and about the
> resulting error message.
>
> Is the query failing because PG doesn't understand the subquery is
> yielding an array? Seems unlikely. But if the problem is a type mismatch
> between 'test' (on the left) and my subquery, I'd expect the same error
> message as if I try to compare an int to a text array:
>
> SELECT 'found' WHERE 1 = ANY( (SELECT ARRAY['test','pass','fail']));
> ERROR: operator does not exist: integer = text[]
> LINE 1: SELECT 'found' WHERE 1 = ANY( (SELECT ARRAY['test','pass','f...
> ^
> HINT: No operator matches the given name and argument type(s). You might
> need to add explicit type casts.
>
> Instead of the error message I actually got:
>
> ERROR: array value must start with "{" or dimension information
>
> Thanks.
>
> Ken
>
>
>
>
>
> On Mon, Sep 16, 2013 at 6:42 PM, bricklen <brick...@gmail.com> wrote:
>
>>
>> On Mon, Sep 16, 2013 at 6:36 PM, Ken Tanzer <ken.tan...@gmail.com> wrote:
>>
>>> Thanks for the explanation. I think I at least understand what it's
>>> doing now. I'm either surprised or confused though, as I was under the
>>> impression that you could substitute a subquery for a value pretty much
>>> anywhere, but I guess that's not the case?
>>>
>>> Cheers,
>>> Ken
>>>
>>>
>> Your subquery can also be explicitly casted to make it work. Note the
>> "::TEXT[]"
>>
>> SELECT 'found' WHERE 'test' = ANY( (SELECT
>> ARRAY['test','pass','fail'])::TEXT[] );
>>
>
>
>
> --
> AGENCY Software
> A data system that puts you in control
> 100% Free Software
> *http://agency-software.org/*
> ken.tan...@agency-software.org
> (253) 245-3801
>
> Subscribe to the mailing
> list<agency-general-requ...@lists.sourceforge.net?body=subscribe>
> to
> learn more about AGENCY or
> follow the discussion.
>
--
AGENCY Software
A data system that puts you in control
100% Free Software
*http://agency-software.org/*
ken.tan...@agency-software.org
(253) 245-3801
Subscribe to the mailing
list<agency-general-requ...@lists.sourceforge.net?body=subscribe>
to
learn more about AGENCY or
follow the discussion.
