PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to assign a column value to NULL in a select, in case this column does not fulfill a condition, using a CASE statement in the WHERE clause?

I have some users in my cluster, whose usernames end with a suffix like "_suffix". Some of them have an username that include a database name plus the suffix, like "dbname_suffix", others have some other name plus the suffix. I am trying to select all the usernames that end with the suffix in one column, matching the users that have the "dbname_suffix" pattern in their usernames with a second column where I have databases.

  1. If the "dbname" part of the username matches the name of a database, then I show them together side by side on both columns;
  2. If the user name does not match the name of any database, then the database column shows the value NULL;
  3. And if there is a database that does not have any username match, then the username column shows the value NULL.

I tried to solve it like this:

select
    pd.datname as DB_NAME,
    pr.rolname as DB_ROLE
from
    pg_roles pr
cross join
    pg_database pd
where
    pr.rolname like ''||pd.datname||'_suffix'
or
    pr.rolname like '%_suffix'
and
    pd.datname not in ('postgres', 'template0', 'template1')
and case
        when
            pr.rolname like '%_suffix' and pr.rolname not like ''||pd.datname||'_suffix'
        then
            pd.datname = 'NULL'
        when
            pd.datname is not null and pr.rolname not like ''||pd.datname||'_suffix' or pr.rolname not like '%_suffix'
        then
            pr.rolname = 'NULL'
    end
order by
    pd.datname;

And I would like to get something like this:

DB_NAME     DB_ROLE
mydb        mydb_suffix
newdb       newdb_suffix
NULL        test_suffix
test_db     NULL

However I am getting something like this:

DB_NAME     DB_ROLE
mydb        mydb_suffix
newdb       newdb_suffix
postgres    postgres_suffix

I am not only getting only usernames that match the pattern "dbname_suffix", but I am also getting the postgres database as output, even though I have set a condition that I don’t want to get this database as an output.

Could anyone show me, please, what I am not doing correctly to get the expected result?

Tags:

2

Answers


  1. 0

    Try a combination of LEFT JOIN and RIGHT JOIN with a UNION to cover all the cases, check below, I combined LEFT JOIN, RIGHT JOIN, and UNION to retrieve matching and non-matching database names and user roles with a specified suffix, ensuring to exclude specific system databases. Hope that helps!

    SELECT 
    pd.datname AS DB_NAME,
    pr.rolname AS DB_ROLE
FROM 
    pg_database pd
LEFT JOIN 
    pg_roles pr
ON 
    pr.rolname = pd.datname || '_suffix'
WHERE 
    pd.datname NOT IN ('postgres', 'template0', 'template1')

UNION

SELECT 
    NULL AS DB_NAME,
    pr.rolname AS DB_ROLE
FROM 
    pg_roles pr
WHERE 
    pr.rolname LIKE '%_suffix'
AND 
    pr.rolname NOT LIKE ANY (SELECT datname || '_suffix' FROM pg_database)

UNION

SELECT 
    pd.datname AS DB_NAME,
    NULL AS DB_ROLE
FROM 
    pg_database pd
WHERE 
    pd.datname NOT IN ('postgres', 'template0', 'template1')
AND 
    pd.datname || '_suffix' NOT IN (SELECT rolname FROM pg_roles)

ORDER BY 
    DB_NAME;
    Login or Signup to reply.
  2. 0

    What you are looking for is a full outer join, not a cross join:

    SELECT pd.datname AS db_name, pr.rolname AS db_role
FROM pg_database pd
FULL JOIN pg_roles pr ON pr.rolname = pd.datname || '_suffix'
WHERE pd.datname NOT IN ('postgres', 'template0', 'template1');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search