Select rows from the array of another select query

Question

The schema is as follows:

create table users (
    id      text constraint users_pk primary key,
    followers     text[]
);

users.followers contains an array of ids in string format.

I want to select those users who are the followers of a certain user, but the following SQL don't not work:

select * 
from users
where id in (
    select followers::text[]
    from users
    where id = '12345678'
);

OR

select * 
from users
where id = any (
    select followers::text[]
    from users
    where id = '2219144865'
);

Both gives the error ERROR: operator does not exist: text = text[]

Kenney · Accepted Answer · 2015-11-28 16:23:07Z

2

You're better of changing your schema: if your id's are numbers, use

create table users(
    id serial not null primary_key
    -- other fields
);

and add a followers table that contains one row per follower:

create table followers (
    user_id      int not null references users(id), 
    follower_id  int not null references users(id),
    unique (user_id, follower_id)
);

and then you can simply select the followers for user with id X like this:

select * from users
where id in (select follower_id from followers where user_id = X)

Relational databases are very good at managing relationships, but you have to use the functionality that they provide for this: tables, rows, foreign keys, and indexes.
It is typically bad practice to store lists of ID's in a column, because that requires special code to split such a text column up into separate ID's. This code must be run for every row in your resultset, slowing things down enormously. PostgreSQL does have extensive support for array columns, but not all databases do (MySQL for instance, does not). It is a PostgreSQL specific feature, and that tells you that this is not the common way to manage relationships in an RDBMS.

Using the two-table approach above, the SQL database can operate very efficiently. For instance, since users.id is a primary key, it is optimized for looking up records by id, using internal hashes.

As a rule of thumb, whenever you have a list or collection of things, you'll want to store them using one row per 'thing'.

Another down side of using arrays here is that the database cannot guarantee that the values in followers actually point to existing users. As far as the database is concerned, it is just a string. By specifying a foreign key the database will ensure data integrity, that is, you cannot store follower id's that do not exist.

You shouldn't worry about space. The text[] array approach might even use more space than using a second table. You can think of the text[] as a 'nested table' inside the users table. It would have to store the number and the length of the entries. In practice it won't make much of a difference. The benefits of using a relationship table like followers far outweigh any concerns about space you might have. This is how RDBMS are meant to operate, and they are very efficient in how they do it.

edited Nov 28, 2015 at 16:23

answered Nov 28, 2015 at 15:29

Kenney

9,12317 silver badges21 bronze badges

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

2 Comments

MK Yung Over a year ago

So you suggested not to store the followers in an array but to flatten them in a table. What are the advantages? Will the new followers table grow to very large? (I came from NoSQL)

Kenney Over a year ago

Updated the answer with an explanation. I hope it makes some sense!

escargot agile · Accepted Answer · 2015-11-28 15:27:42Z

0

It seems to me that the reason for the error is that the inner select query does not return just an array, but a set of arrays. Every followers is an array by itself, and the select returns many arrays of followers. An ID is then not a member of the resulting set, because an ID is a text, not an array.

You need to concatenate them into one big array, and then you can check whether the uses exists there or not. So I would suggest something like the following (I'm not sure the syntax is right):

select * 
from users
where id in array_cat(
    select followers::text[]
    from users
    where id = '12345678'
);

answered Nov 28, 2015 at 15:27

escargot agile

22.4k15 gold badges88 silver badges144 bronze badges

2 Comments

MK Yung Over a year ago

Sorry it still doesnt work, and I think I better follow @kenney 's advice not to use array ;) Thanks!

escargot agile Over a year ago

I agree, I like his answer too.

Collectives™ on Stack Overflow

Select rows from the array of another select query

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related