PostgreSQL query involving integer[]

Question

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.

Peter Eisentraut · Accepted Answer · 2011-01-21 21:37:07Z

4

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.

answered Jan 21, 2011 at 21:37

Peter Eisentraut

37.1k12 gold badges91 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Milen A. Radev · Accepted Answer · 2011-01-21 17:03:55Z

4

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.

edited Jan 21, 2011 at 17:03

answered Jan 21, 2011 at 16:58

Milen A. Radev

63.1k22 gold badges111 silver badges112 bronze badges

Comments

score 2 · Accepted Answer · 2011-01-21 17:38:52Z

2

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

edited Jan 21, 2011 at 17:38

answered Jan 21, 2011 at 17:10

user330315

2 Comments

vector Over a year ago

... bummer, I'm 8.3, both dev and prod :-(

vector Over a year ago

... you bet me to it, that's what I found :-)

Collectives™ on Stack Overflow

PostgreSQL query involving integer[]

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related