Re: Update "usename" in pg_user

On 2023-Nov-14, Bernd Lentes wrote:

> It didn't work. I got this message (unfortunately in german):
> postgres=# update pg_user set usename = 'usename(at)helmholtz-munich(dot)de' where usename = 'dorota.germann';
> FEHLER: kann Sicht »pg_shadow« nicht aktualisieren
> DETAIL: Sichten, die nicht aus einer einzigen Tabelle oder Sicht lesen, sind nicht automatisch aktualisierbar.
> HINT: Um Aktualisieren der Sicht zu ermöglichen, richten Sie einen INSTEAD OF UPDATE Trigger oder eine ON UPDATE DO INSTEAD Regel ohne Bedingung ein.

This error says that you're trying to update a view (Sicht), and
suggesting to update the underlying table instead. So you would update
pg_authid, which is the table that the pg_user and pg_shadow views are
based on (and the column would be "rolname", not "usename"). Also, you
probably wanted the SET clause as
SET usename = usename || '@helmholtz-munich.de'
otherwise all users would end up with the same username (or actually got
an error that the second user would get a duplicate name).

However, using UPDATE (or any DML) on system catalogs is not a great
move. Using \gexec as already suggested by Laurenz is probably your
best bet.

BTW, you can use
SET lc_messages to "C";
before the UPDATE to get these error messages in English.

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"The Postgresql hackers have what I call a "NASA space shot" mentality.
Quite refreshing in a world of "weekend drag racer" developers."
(Scott Marlowe)