Integer array lookup using Postgres

Question

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?

Answer 1

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.