Pagico Flex's answer is inspiring though it's not completed and not working at least on my Odoo 13 CE.

But, upon further exploration to the issue, just learn more about the Odoo password.

First, the gen_salt function inside PostgreSQL is unknown but after CREATE EXTENSION crypt, then it becomes known.

Second, even gen_salt known but it doesn't support sha1 algorithm.

Third, whether odoo is uing sha1 unknown.

I have a select id, login, password from res_users; i found all password (encrypted) in db share a same format liked
$pbkdf2-sha512$25000$dC6ltJZSas2 .............

from start up to $25000$ are the same and in between a string sha512... does it mean it's using sha512?
sha512 also not supported by gen_salt

moreover, an identical string as prefix means concatenated encrypted password?

i am still looking for a solution for a SQL to change odoo user password altogether.

any idea is appreciated.

I finally found the answer myself. It is very interesting and so simple that
update res_users set password = 'new_password' where id = xx;
that's it. simply give a password in plain text, no any complicated md5(), crypt() functions required.
then in psql,
select id, password you'll see the password in plain text, but....
you can login.
when u use backend to change password, the pw in db is encrypted.
very happy but do spend so much time on this, why didn't i just try the most simple way to update from the very beginning???

md5 is not work.
upon issued the update password SQL using md5() the password stored in res_users not liked other passwords created from backend.
e.g. the length of encrypted password much shorter.