How can I create a bool column for a view based on the result of a statement?

Question

I'm using PostgreSQL as my database.

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

CREATE TABLE users (
  id        SERIAL        NOT NULL,
  group     INT           NOT NULL,
  name      TEXT          NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (group)
    REFERENCES groups(id),
  UNIQUE (group),
  UNIQUE (name)
);

I want to create a view that includes a boolean column stating whether or not a group has any users based on the has many / belongs to relationship of the two tables above. If it's easier, I'd even be fine with an integer column stating how many users a group has in it. How can I do this in my view?

CREATE VIEW group_info AS
  SELECT DISTINCT
    g.name        AS name,
    ????          AS empty
  FROM groups AS g
  JOIN users AS u
    ON (g.id = u.group)
;

I've looked into using BOOL_OR and/or COUNT to help with testing the existence of a group ID in the users table, but I'm not seeing how to put it all together.

Answer 1

"a boolean column stating whether or not a group has any users"

Use EXISTS:

CREATE VIEW group_info AS
SELECT g.name, NOT EXISTS (SELECT 1 FROM users u WHERE u.group = g.id) AS empty
FROM   groups g;

This returns 1 row per group, no matter whether there are users or not - not one row per user like you had, but probably didn't want - so we don't need DISTINCT.

It's largely irrelevant whether you write SELECT 1 or SELECT * or SELECT 'foo' in the EXISTS subquery. Only the bare existence of at least one row matters.

With number of users

CREATE VIEW group_info AS
SELECT g.name, count(u.group) AS ct_users
FROM   groups     g
LEFT   JOIN users u ON u.group = g.id
GROUP  BY g.name;

LEFT JOIN is crucial to retain groups without users.