current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

Let's say I have a table called tag: 

CREATE TABLE tag (
  id SERIAL PRIMARY KEY,
  text TEXT NOT NULL UNIQUE
);

And I use integer arrays on other tables to reference those tags:

CREATE TABLE device (
  id SERIAL PRIMARY KEY,
  tag_ids INTEGER[] NOT NULL DEFAULT '{}',
);

What is the simplest and most efficient way that I can map the tag_ids to the appropriate rows in tag such that I can query the device table and the results will include a tags column with the text of each tag in a text array?

I understand that this is not the preferred technique and has a number of significant disadvantages. I understand that there is no way to enforce referential integrity in arrays. I understand that a many-to-many join would be much simpler and probably better way to implement tagging.

The database normalization lectures aside, is there a graceful way to do this in postgres? Would it make sense to write a function to accomplish this?

share|improve this question
1  
You want intarray with its gin and gist index implementations for arrays of integer and the contains tests <@, @>. –  Craig Ringer May 7 at 4:18
    
@CraigRinger- Currently I am declaring it as INTEGER[] then manually creating the appropriate GIN index- is it better to specifically use the intarray module? –  dgel May 7 at 4:21
    
intarray provides index opclasses that are more efficient IIRC. Also, if you insert rows much you're going to be better off with GiST than GIN. –  Craig Ringer May 7 at 4:23
    
@CraigRinger- I'll look into that. Thanks. –  dgel May 7 at 4:24

2 Answers 2

up vote 0 down vote

I would recommend a combination of unnest and join for this.

i.e. something of the form:

select unnest(t.tag_ids) as tag_id, d.*
from device as d
join tag as t ON (d.tag_id = d.id)
order by d.tag_id;
share|improve this answer
up vote 1 down vote

Untested, but IIRC:

SELECT 
    device.*, t."text"
FROM 
    device d
    left outer join tag t on ( ARRAY[t.id] @> d.tag_ids)

should be able to use a GiST or GIN index on d.tag_ids. That's also useful for queries where you want to say "find rows containing tag [x]".

I might've got the direction of the @> operator wrong, I always get it muddled. See the array operators docs for details.

The intarray module provides a gist opclass for arrays of integer which I'd recommend using; it's more compact and faster to update.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.