postgresql delete role with similar names

postgresql, postgresql-9.2, psql, roles

Solution

Without using dynamic SQL of some sort, no, you can't do that. A simple example using dynamic SQL to drop the roles:

select 'DROP ROLE ' || rolname || ';' from pg_roles where rolname like 'abc_%';

If you are unwilling to paste the results into a psql session, you can also pipe it from one session to another.

psql -d yourdb -U youruser -qtAc "select 'DROP ROLE ' || rolname || ';' from pg_roles where rolname like 'abc_%'" | psql -d yourdb -U youruser

Problem

I have created roles with similar names like abc_ where number varies but abc_ always stays same. I can see the roles with following query ``` select * from pg_roles where rolname like 'abc_%'; ``` But I don't know how to drop all role with similar name. I have got following query but it takes full name. ``` DROP ROLE name; ``` I am trying to dropping from psql and I am not writing any functions. Is there any query to to drop roles where I can have like 'abc_%'?

Original source