PostgreSQL – How to Aggregate Multiple Results Using BOOL_AND

postgresqlview

I'm using PostgreSQL as my database.

CREATE TABLE users (
  id        SERIAL        NOT NULL,
  name      TEXT          NOT NULL,
  PRIMARY KEY (id),
  UNIQUE (name)
);

CREATE TABLE groups (
  id        SERIAL        NOT NULL,
  name      TEXT          NOT NULL,
  PRIMARY KEY (id),
  UNIQUE (name)
);

CREATE TABLE associations (
  user      INT           NOT NULL,
  group     INT           NOT NULL,
  admin     BOOLEAN       NOT NULL DEFAULT false,
  PRIMARY KEY (user, group),
  FOREIGN KEY (user)
    REFERENCES users(id),
  FOREIGN KEY (group)
    REFERENCES groups(id)
);

Similar to another question I posted here, how can I compare the result of multiple rows from another table and aggregate them in a view, perhaps using something like BOOL_AND?

CREATE VIEW user_info AS
  SELECT DISTINCT
    u.id              AS id,
    u.name            AS name,
    EXISTS (
      SELECT 1 FROM associations AS a
      WHERE (u.id = a.user AND a.admin = true)
    )                 AS is_admin,
    BOOL_AND(???)     AS is_complete_admin
  FROM users AS u
;

Here, I'm wanting the is_complete_admin to be true if every row in associations for a particular user has admin = true.

Again, I think BOOL_AND is an option here, but I'm just not sure how to put it all together.

Best Answer

select
    u.id,
    u.name,
    bool_or (a.admin) as is_admin,
    bool_and (a.admin) as is_complete_admin
from
    users u
    inner join
    associations a on u.id = a.user
group by u.id, u.name
Related Question