Re: Why does this array query fail?

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(dot)tanzer(at)gmail(dot)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 <bricklen(at)gmail(dot)com> wrote:
>
>>
>> On Mon, Sep 16, 2013 at 6:36 PM, Ken Tanzer <ken(dot)tanzer(at)gmail(dot)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(dot)tanzer(at)agency-software(dot)org
> (253) 245-3801
>
> Subscribe to the mailing list<agency-general-request(at)lists(dot)sourceforge(dot)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(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing
list<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe>
to
learn more about AGENCY or
follow the discussion.