Postgres: JOIN on an array field

gary

9 years ago

Say we create two tables (users and groups), where users are in groups:

create table users (
  id int unique,
  name varchar unique
);

create table groups (
  id int unique,
  name varchar unique,
  member_users int[],
  member_groups int[]
);

The groups own the user memberships, rather than having an intermediate table with the relation.

Now, we want to join them, so we can simply do this:

select *
from groups join users
  on users.id = ANY (groups.member_users)