PostgreSQL query involving integer[]

I have 2 tables:

CREATE TABLE article (  
  id serial NOT NULL,
  title text,
  tags integer[] -- array of tag id's from TAG table
)

CREATE TABLE tag (
  id serial NOT NULL,
  description character varying(250) NOT NULL
)

... and need to select tags from TAG table held in ARTICLE's 'tags integer[]' based on article's title.

So tried something like

SELECT *  
  FROM tag 
 WHERE tag.id IN ( (select article.tags::int4 
                      from article 
                     where article.title = 'some title' ) );

... which gives me

ERROR: cannot cast type integer[] to integer

LINE 1: ...FROM tag WHERE tag.id IN ( (select article.tags::int4 from ...开发者_开发百科

I am Stuck with PostgreSql 8.3 in both dev and production environment.

Use the array overlaps operator &&:

SELECT *
    FROM tag
    WHERE ARRAY[id] && ANY (SELECT tags FROM article WHERE title = '...');

Using contrib/intarray you can even index this sort of thing quite well.

Take a look at section "8.14.5. Searching in Arrays", but consider the tip at the end of that section:

Tip: Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

You did not mention your Postgres version, so I assume you are using an up-to-date version (8.4, 9.0)

This should work then:

SELECT *  
  FROM tag 
 WHERE tag.id IN ( select unnest(tags)
                     from article 
                    where title = 'some title' );

But you should really consider changing your table design.

Edit

For 8.3 the unnest() function can easily be added, see this wiki page:
http://wiki.postgresql.org/wiki/Array_Unnest