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.
- If the "dbname" part of the username matches the name of a database, then I show them together side by side on both columns;
- If the user name does not match the name of any database, then the database column shows the value NULL;
- 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?
2
Answers
Try a combination of
LEFT JOIN
andRIGHT JOIN
with aUNION
to cover all the cases, check below, I combinedLEFT JOIN
,RIGHT JOIN
, andUNION
to retrieve matching and non-matching database names and user roles with a specified suffix, ensuring to exclude specific system databases. Hope that helps!What you are looking for is a full outer join, not a cross join: