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?

You want intarray with its gin and gist index implementations for arrays of integer and the contains tests <@, @>. — Craig Ringer
– Craig Ringer, Commented May 7, 2014 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
– dgel, Commented May 7, 2014 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
– Craig Ringer, Commented May 7, 2014 at 4:23

Craig Ringer · Accepted Answer · 2014-05-07 04:28:17Z

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.

answered May 7, 2014 at 4:28

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

Comments

khampson · Accepted Answer · 2014-05-07 04:21:13Z

0

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;

answered May 7, 2014 at 4:21

khampson

15.5k4 gold badges45 silver badges44 bronze badges

Collectives™ on Stack Overflow

Integer array lookup using Postgres

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related