Search code examples
postgresqlroles

Postgresql: user role with "\" in name

I made a typo while creating a role. Now I'm stuck with "\ruser" and I have no clue how to properly drop that role. Escaping the backslash in any kind of way doesn't work, using unicode formating doesnt work (drop role U&'%0Dmyuser';) or any kind of string markings (",',`) won't work. Any ideas?

server=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 \rmyuser  | Superuser, Create role, Create DB                          | {}
 myuser    | Superuser, Create role, Create DB                          | {}
Solution

  • Simply escape the identifier with double quotes:

    DROP ROLE "\rmyuser";

    In the case that \r is not a backslash and an r, but an escaped version of "carriage return", the simplest way may be to use dynamic SQL, the extended string literal syntax and the format function:

    DO $$BEGIN EXECUTE format('DROP ROLE %I', E'\rmyuser'); END;$$;