Postgres - Exclusão de uma parte do campo, com base em outro campo.

Bom Dia, referente ao que está no enunciado, o problema é que não existe uma função padrão para isto.
Opção, criar uma função de varredura da tabela, no seu caso de endereços temos:
1º A opção que tudo começa no primeiro caracter do registro;
Ex: Rua ou R., lembrando de outros como Avenida -Av. (maiúsculas, minúsculas, com ponto espaço etc.)
Neste caso se localizar com a função REPLACE() cada uma a ser pesquisada faça uma lista;
Exemplo: TRIM(REPLACE("Rua José Bonifácio" ,"Rua ", ""))

2º Removendo com cuidado pois outras palavras Ex: Rua Ruanda ou com final como Construa, etc.

3º Veja também a questão de números, e caracteres que não fazem parte do nome do logradouro.

4º) Outra opção é a função SUBSTRING(), SUBSTR(), no caso não encontrar começa da posição 0.
Exemplo: SUBSTRING("Rua José Bonifácio", POSITION("Rua " IN "Rua José Bonifácio"));