Search Postgresql Archives

Re: Why does this array query fail?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> wrote:

On Mon, Sep 16, 2013 at 6:36 PM, Ken Tanzer <ken.tanzer@xxxxxxxxx> 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.tanzer@xxxxxxxxxxxxxxxxxxx
(253) 245-3801

Subscribe to the mailing list to
learn more about AGENCY or
follow the discussion.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux