Postgres: JOIN on an array field

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)

Leave a Reply

Your email address will not be published. Required fields are marked *