0

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.

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.

  • Thanks Erwin. Can you help me understand how to include this as part of my view I'm trying to create? – Bryan Dec 18 '14 at 18:15
  • For completeness, how would I get the number of users rather than just the existence of users? – Bryan Dec 18 '14 at 18:54
  • @Bryan: That's a different query, typically more expensive with big tables and/or many users per group. – Erwin Brandstetter Dec 18 '14 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 '14 at 23:21
  • @Bryan: Oh, because my query was missing the last line. Sorry, fixed. – Erwin Brandstetter Dec 19 '14 at 2:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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