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?