Merge rows from union, replacing null columns with ones with value

Question

I have a query that makes a UNION out of these two other queries. What I'd like to do is group these results by organizationId and userId, but that's giving me an error.

ERROR: column "user_organizations.admin" must appear in the GROUP BY clause or be used in an aggregate function LINE 4: user_organizations.admin,

It's asking to add ALL the columns to the GROUP BY.

Here's the query:

SELECT
    user_organizations. "organizationId",
    user_organizations. "userId",
    user_organizations.admin,
    user_organizations.owner,
    user_organizations.member
FROM ((
        SELECT
            organizations.id AS "organizationId",
            organizations. "ownedById" AS "userId",
            NULL AS admin,
            NULL AS member,
            TRUE AS OWNER
        FROM
            organizations)
    UNION (
        SELECT
            organizations.id AS "organizationId",
            memberships. "userId",
            memberships.admin,
            TRUE AS member,
            NULL AS OWNER
        FROM
            memberships
        LEFT JOIN organizations ON memberships. "organizationId" = organizations.id)) AS user_organizations;

Here are the results itself:

You can see that there are two columns with 1, 4, I'd like to merge these columns so there's only one row, where it is 1, 4, TRUE, TRUE, TRUE. Is it possible to merge rows like this, and upsert the NULL value?

Answer 1

This looks like it can be simplified quite a bit:

SELECT
    coalesce("organizationId", organizations.id) as "organizationId",
    coalesce("ownedById", "userId") as "userId",
    memberships.admin,
    CASE WHEN memberships.id IS NULL THEN TRUE ELSE NULL END AS owner,
    CASE WHEN memberships.id IS NOT NULL THEN TRUE ELSE NULL END AS member
FROM organizations
FULL OUTER JOIN memberships on memberships."organizationId" = organizations.id
            AND memberships."userId" = organizations."ownedById"
;

Answer 2

WITH org AS (
    SELECT
        organizations.id AS "organizationId",
        organizations. "ownedById" AS "userId",
        TRUE AS OWNER,
        NULL AS admin,
        NULL AS member
    FROM
        organizations
),
memberships_free AS (
    SELECT
        memberships. "organizationId" AS "organizationId",
        memberships. "userId" AS "userId",
        CASE WHEN org.owner is NULL THEN FALSE ELSE org.owner END AS owner,
--        org.owner as owner,
        memberships.admin,
        TRUE:: boolean AS member
    FROM
        memberships
    LEFT JOIN org ON memberships. "userId" = org. "userId"
        AND memberships. "organizationId" = org. "organizationId"
),
organizations_not_in_memberships AS (
    SELECT
        *
    FROM
        organizations
WHERE
    NOT EXISTS (
        SELECT
            *
        FROM
            memberships_free
        WHERE
            memberships_free. "organizationId" = organizations.id
            AND memberships_free. "userId" = organizations. "ownedById")
),
org_appendable AS (
    SELECT
        organizations_not_in_memberships.id AS "organizationId",
        organizations_not_in_memberships. "ownedById" AS "userId",
        TRUE:: boolean AS OWNER,
        FALSE AS admin,
        FALSE AS member
    FROM
        organizations_not_in_memberships
)

SELECT * FROM memberships_free
UNION
SELECT * FROM org_appendable

or

 WITH org AS (
         SELECT organizations.id AS "organizationId",
            organizations."ownedById" AS "userId",
            true AS owner,
            NULL::text AS admin,
            NULL::text AS member
           FROM organizations
        ), memberships_free AS (
         SELECT memberships."organizationId",
            memberships."userId",
                CASE
                    WHEN org.owner IS NULL THEN false
                    ELSE org.owner
                END AS owner,
            memberships.admin,
            true AS member
           FROM memberships
             LEFT JOIN org ON memberships."userId" = org."userId" AND memberships."organizationId" = org."organizationId"
        ), organizations_not_in_memberships AS (
         SELECT organizations.id,
            organizations.uuid,
            organizations."displayName",
            organizations.name,
            organizations."deletedAt",
            organizations."createdAt",
            organizations."updatedAt",
            organizations."createdById",
            organizations."ownedById"
           FROM organizations
          WHERE NOT (EXISTS ( SELECT memberships_free."organizationId",
                    memberships_free."userId",
                    memberships_free.owner,
                    memberships_free.admin,
                    memberships_free.member
                   FROM memberships_free
                  WHERE memberships_free."organizationId" = organizations.id AND memberships_free."userId" = organizations."ownedById"))
        ), org_appendable AS (
         SELECT organizations_not_in_memberships.id AS "organizationId",
            organizations_not_in_memberships."ownedById" AS "userId",
            true AS owner,
            false AS admin,
            false AS member
           FROM organizations_not_in_memberships
        )
 SELECT memberships_free."organizationId",
    memberships_free."userId",
    memberships_free.owner,
    memberships_free.admin,
    memberships_free.member
   FROM memberships_free
UNION
 SELECT org_appendable."organizationId",
    org_appendable."userId",
    org_appendable.owner,
    org_appendable.admin,
    org_appendable.member
   FROM org_appendable;

Answer 3

You need to perform the Group by on first two column and use the aggregate on rest of three column.

SELECT
    user_organizations. "organizationId",
    user_organizations. "userId",
    max(user_organizations.admin) as admin,
    max(user_organizations.owner) as owner,
    max(user_organizations.member) member
FROM ((
        SELECT
            organizations.id AS "organizationId",
            organizations. "ownedById" AS "userId",
            NULL AS admin,
            NULL AS member,
            TRUE AS OWNER
        FROM
            organizations)
    UNION (
        SELECT
            organizations.id AS "organizationId",
            memberships. "userId",
            memberships.admin,
            TRUE AS member,
            NULL AS OWNER
        FROM
            memberships
        LEFT JOIN organizations ON memberships. "organizationId" = organizations.id)) AS user_organizations
group by user_organizations. "organizationId",
    user_organizations. "userId"