How to fix Role already exists in PostgreSQL

First, verify whether the role already exists in your PostgreSQL instance:

sudo -u postgres psql -c "\\du"

Or query directly:

SELECT rolname FROM pg_roles WHERE rolname = 'my_user';

If the role appears in the results, it already exists and doesn't need to be created.

Wrap your CREATE ROLE statement in an anonymous code block that catches the duplicate_object exception:

DO $$
BEGIN
  CREATE ROLE my_user LOGIN PASSWORD 'my_password';
EXCEPTION
  WHEN duplicate_object THEN
    RAISE NOTICE 'Role my_user already exists, skipping.';
END
$$;

This approach allows the script to continue even if the role exists. The WHEN duplicate_object clause catches the duplicate error and simply logs a notice instead of failing.

For more control, check the pg_roles catalog before attempting creation:

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'my_user') THEN
    CREATE ROLE my_user LOGIN PASSWORD 'my_password';
    RAISE NOTICE 'Role my_user created.';
  ELSE
    RAISE NOTICE 'Role my_user already exists.';
  END IF;
END
$$;

This gives you visibility into whether the role was created or already existed.

If the role exists and you need to change its properties, use ALTER ROLE instead:

ALTER ROLE my_user WITH LOGIN PASSWORD 'new_password';

You can change login privileges, password, superuser status, and other attributes without recreating the role. Check the existing role first:

SELECT * FROM pg_roles WHERE rolname = 'my_user';

When working with pg_dump, you have options to handle existing roles:

Option 1: Drop existing roles before restoring

psql -U postgres -d your_database -c "DROP ROLE IF EXISTS my_user;"
psql -U postgres -d your_database -f dump.sql

Option 2: Dump without role definitions

pg_dump --no-owner --no-acl source_db > dump.sql

Then roles must exist before you restore:

psql -U postgres -d target_db -f dump.sql

Option 2 is safer for production environments where role management is separate from object ownership.