Skip site navigation (1) Skip section navigation (2)

Fail to search in array, produced by subquery - is it a bug?

From: Dmitry Fefelov <fozzy(at)ac-sw(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Fail to search in array, produced by subquery - is it a bug?
Date: 2011-04-27 03:29:03
Message-ID: 201104271029.03301.fozzy@ac-sw.com (view raw or flat)
Thread:
Lists: pgsql-hackers
With Postgres 8.4 query like 

SELECT *
  FROM core.tag_links ctl
  WHERE (ctl.tag_id = ANY (
      SELECT array_agg(ct.id) 
        FROM core.tags ct
        WHERE (LOWER(ct.tag) LIKE LOWER(('search tag')::text || '%') ESCAPE
            E'\\')
    ));

produces error:

ERROR:  operator does not exist: bigint = bigint[]
ROW 3:  WHERE (ctl.tag_id = ANY (
                             ^
HINT:  No operator matches the given name and argument type(s). You might need 
to add explicit type casts.

Bith ct.id and ctl.tag_id - of type BIGINT.

Result of 

SELECT array_agg(ct.id) 
        FROM core.tags ct
        WHERE (LOWER(ct.tag) LIKE LOWER(('search tag')::text || '%') ESCAPE
            E'\\');

                                                                         array_agg                                                                                                                              
-----------------------------------------------------------------------------------------------------------------------------
 {54426,53600,54062,187207,187642,54395,53312,51912,128621,19203,6613,54462}
(1 row)

Should ANY (...)  and ALL (...) work when "..." is a subquery, returning 
single ARRAY field, or maybe  I misunderstood something?

Regards, 
Dmitry

Responses

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2011-04-27 03:36:32
Subject: Re: offline consistency check and info on attributes
Previous:From: Greg StarkDate: 2011-04-27 03:12:02
Subject: Re: "stored procedures" - use cases?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group