I'm using PostgreSQL as my database.

  id        SERIAL        NOT NULL,
  name      TEXT          NOT NULL,
  UNIQUE (name)

  id        SERIAL        NOT NULL,
  group     INT           NOT NULL,
  name      TEXT          NOT NULL,
  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
    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.

1 Answer 1


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


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.

  • Thanks Erwin. Can you help me understand how to include this as part of my view I'm trying to create?
    – Bryan
    Dec 18, 2014 at 18:15
  • For completeness, how would I get the number of users rather than just the existence of users?
    – Bryan
    Dec 18, 2014 at 18:54
  • @Bryan: That's a different query, typically more expensive with big tables and/or many users per group. Dec 18, 2014 at 18:58
  • Thanks Erwin... any idea why I'm getting an error saying "g.name" must appear in the GROUP BY clause when I try to create the view?
    – Bryan
    Dec 18, 2014 at 23:21
  • @Bryan: Oh, because my query was missing the last line. Sorry, fixed. Dec 19, 2014 at 2:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.