title: PostgreSQL retrieve records with items in array with array_agg
body: <p>My database system is PostgreSQL 8.4</p>
  
  <p>The following is the example schema:</p>
  
  <pre><code>CREATE TABLE items(
    item_code varchar(20) NOT NULL PRIMARY KEY,
    item_description varchar(200),
    item_pieces_per_pack integer
  );
  
  CREATE TABLE customer_orders (
    order_id integer,
    item_code varchar(20),
    required_shipping_date date,
    quantity integer,
    PRIMARY KEY (order_id ,item_code,required_shipping_date)
  );
  
  INSERT INTO items VALUES ('AL0000063','PANETTONE CLASSICO BASSO 1KG',1);
  INSERT INTO items VALUES ('AL0000064','PANDORO CLASSICO 1KG',1);
  INSERT INTO items VALUES ('AL0000065','TORRONE CIOCCOLATO E NOCCIOLE 250G',4);
  INSERT INTO items VALUES ('AL0000066','CANTUCCI TOSCANI 100G',6);
  INSERT INTO items VALUES ('AL0000067','SAGRANTINO DI MONTEFALCO',6);
  INSERT INTO items VALUES ('AL0000068','BAROLO',6);
  INSERT INTO items VALUES ('AL0000069','AMARONE',6);
  
  
  INSERT INTO customer_orders VALUES (1,'AL0000063','2013-12-15',5);
  INSERT INTO customer_orders VALUES (1,'AL0000066','2013-12-15',15);
  INSERT INTO customer_orders VALUES (1,'AL0000067','2013-12-14',15);
  INSERT INTO customer_orders VALUES (2,'AL0000063','2013-12-11',25);
  INSERT INTO customer_orders VALUES (2,'AL0000064','2013-12-11',25);
  INSERT INTO customer_orders VALUES (2,'AL0000067','2013-12-13',25);
  INSERT INTO customer_orders VALUES (3,'AL0000063','2013-12-13',25);
  INSERT INTO customer_orders VALUES (3,'AL0000065','2013-12-10',10);
  INSERT INTO customer_orders VALUES (3,'AL0000065','2013-12-20',10);
  </code></pre>
  
  <p>I need to retrieve from the table "items" only record with item_code in a array.
  The following query work fine:</p>
  
  <pre><code>SELECT * FROM items
  WHERE item_code = ANY('{AL0000063,AL0000064,AL0000065}'::text[])
  </code></pre>
  
  <p>But when I use a sub query with array_agg:</p>
  
  <pre><code>SELECT * FROM items
  WHERE item_code = ANY(
    SELECT array_agg(DISTINCT item_code) 
    FROM customer_orders 
    WHERE required_shipping_date BETWEEN '2013-12-01' AND '2013-12-15')
  </code></pre>
  
  <p>PostgreSQL raises the error:<br/></p>
  
  <p>ERROR:  operator does not exist: character varying = character varying[] LINE 2: WHERE item_code = ANY(
                          ^ HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.</p>
  
  <p><br/>
  Why?</p>