Fórum Ordenação cadeia caracteres Postgres #487298

Boa noite pessoal, estou com o seguinte problema: tenha minha tabela conta_a e a mesma é formada geralmente assim 3.1.01 as vezes pode ser inserido 3.1.10, não estou conseguindo ordenar abaixo tenho todos os códigos como quero que fique.

preciso ordenar assim | consulta ordenando codigo como varchar | consulta ordenando replace(codigo,'.','')::int8 |
-----------------------+----------------------------------------+---------------------------------------------------+
'3.1' | '3.1' | '3.1'
'3.1.01' | '3.1.01' | '3.2'
'3.1.02' | '3.1.02' | '3.3'
'3.1.03' | '3.1.03' | '3.4'
'3.1.04' | '3.1.04' | '3.5'
'3.1.05' | '3.1.05' | '3.6'
'3.1.06' | '3.1.06' | '3.7'
'3.1.07' | '3.1.07' | '3.8'
'3.1.08' | '3.1.08' | '3.10'
'3.1.09' | '3.1.09' | '3.11'
'3.1.10' | '3.1.10' | '3.12'
'3.1.11' | '3.1.11' | '3.13'
'3.1.12' | '3.1.12' | '3.6.1'
'3.1.13' | '3.1.13' | '3.6.2'
'3.1.14' | '3.1.14' | '3.7.1'
'3.1.15' | '3.1.15' | '3.7.2'
'3.1.16' | '3.1.16' | '3.7.3'
'3.1.17' | '3.1.17' | '3.7.4'
'3.1.18' | '3.1.18' | '3.7.5'
'3.1.19' | '3.1.19' | '3.7.6'
'3.1.20' | '3.1.20' | '3.7.7'
'3.1.21' | '3.1.21' | '3.7.8'
'3.1.22' | '3.1.22' | '3.1.01'
'3.1.23' | '3.1.23' | '3.10.1'
'3.1.24' | '3.1.24' | '3.1.02'
'3.2' | '3.10' | '3.10.2'
'3.2.01' | '3.10.1' | '3.1.03'
'3.2.02' | '3.10.2' | '3.1.04'
'3.2.03' | '3.11' | '3.1.05'
'3.2.04' | '3.11.1' | '3.1.06'
'3.2.05' | '3.11.2' | '3.1.07'
'3.2.06' | '3.11.3' | '3.1.08'
'3.2.07' | '3.12' | '3.1.09'
'3.2.08' | '3.12.1' | '3.1.10'
'3.2.09' | '3.12.2' | '3.1.11'
'3.2.10' | '3.12.3' | '3.11.1'
'3.2.11' | '3.12.4' | '3.11.2'
'3.2.12' | '3.13' | '3.1.12'
'3.2.13' | '3.13.1' | '3.1.13'
'3.2.14' | '3.13.10' | '3.11.3'
'3.2.15' | '3.13.11' | '3.1.14'
'3.2.16' | '3.13.12' | '3.1.15'
'3.2.17' | '3.13.2' | '3.1.16'
'3.2.18' | '3.13.3' | '3.1.17'
'3.2.19' | '3.13.4' | '3.1.18'
'3.2.20' | '3.13.5' | '3.1.19'
'3.2.21' | '3.13.6' | '3.1.20'
'3.2.22' | '3.13.7' | '3.12.1'
'3.2.23' | '3.13.8' | '3.1.21'
'3.2.24' | '3.13.9' | '3.12.2'
'3.2.25' | '3.2' | '3.1.22'
'3.2.26' | '3.2.01' | '3.1.23'
'3.2.27' | '3.2.02' | '3.12.3'
'3.2.28' | '3.2.03' | '3.12.4'
'3.2.29' | '3.2.04' | '3.1.24'
'3.2.30' | '3.2.05' | '3.13.1'
'3.2.31' | '3.2.06' | '3.13.2'
'3.2.32' | '3.2.07' | '3.13.3'
'3.2.33' | '3.2.08' | '3.13.4'
'3.2.34' | '3.2.09' | '3.13.5'
'3.2.35' | '3.2.10' | '3.13.6'
'3.2.36' | '3.2.11' | '3.13.7'
'3.2.37' | '3.2.12' | '3.13.8'
'3.2.38' | '3.2.13' | '3.13.9'
'3.3' | '3.2.14' | '3.2.01'
'3.3.01' | '3.2.15' | '3.2.02'
'3.3.02' | '3.2.16' | '3.2.03'
'3.3.03' | '3.2.17' | '3.2.04'
'3.3.04' | '3.2.18' | '3.2.05'
'3.3.05' | '3.2.19' | '3.2.06'
'3.3.06' | '3.2.20' | '3.2.07'
'3.3.07' | '3.2.21' | '3.2.08'
'3.3.08' | '3.2.22' | '3.2.09'
'3.3.10' | '3.2.23' | '3.2.10'
'3.4' | '3.2.24' | '3.2.11'
'3.4.01' | '3.2.25' | '3.2.12'
'3.4.02' | '3.2.26' | '3.2.13'
'3.4.03' | '3.2.27' | '3.2.14'
'3.4.04' | '3.2.28' | '3.2.15'
'3.4.05' | '3.2.29' | '3.2.16'
'3.4.06' | '3.2.30' | '3.2.17'
'3.4.07' | '3.2.31' | '3.2.18'
'3.4.08' | '3.2.32' | '3.2.19'
'3.4.09' | '3.2.33' | '3.2.20'
'3.4.10' | '3.2.34' | '3.2.21'
'3.4.11' | '3.2.35' | '3.2.22'
'3.4.12' | '3.2.36' | '3.2.23'
'3.4.13' | '3.2.37' | '3.2.24'
'3.4.14' | '3.2.38' | '3.2.25'
'3.5' | '3.3' | '3.2.26'
'3.5.01' | '3.3.01' | '3.2.27'
'3.5.02' | '3.3.02' | '3.2.28'
'3.5.03' | '3.3.03' | '3.2.29'
'3.5.04' | '3.3.04' | '3.2.30'
'3.5.05' | '3.3.05' | '3.2.31'
'3.5.06' | '3.3.06' | '3.2.32'
'3.5.07' | '3.3.07' | '3.2.33'
'3.5.08' | '3.3.08' | '3.2.34'
'3.5.09' | '3.3.10' | '3.2.35'
'3.5.10' | '3.4' | '3.2.36'
'3.5.11' | '3.4.01' | '3.2.37'
'3.5.12' | '3.4.02' | '3.2.38'
'3.5.13' | '3.4.03' | '3.3.01'
'3.6' | '3.4.04' | '3.3.02'
'3.6.1 ' | '3.4.05' | '3.3.03'
'3.6.2 ' | '3.4.06' | '3.3.04'
'3.7' | '3.4.07' | '3.3.05'
'3.7.1 ' | '3.4.08' | '3.3.06'
'3.7.2 ' | '3.4.09' | '3.3.07'
'3.7.3 ' | '3.4.10' | '3.3.08'
'3.7.4 ' | '3.4.11' | '3.3.10'
'3.7.5 ' | '3.4.12' | '3.4.01'
'3.7.6 ' | '3.4.13' | '3.4.02'
'3.7.7 ' | '3.4.14' | '3.4.03'
'3.7.8 ' | '3.5' | '3.4.04'
'3.8' | '3.5.01' | '3.4.05'
'3.8.01' | '3.5.02' | '3.4.06'
'3.8.02' | '3.5.03' | '3.4.07'
'3.8.03' | '3.5.04' | '3.4.08'
'3.8.04' | '3.5.05' | '3.4.09'
'3.8.05' | '3.5.06' | '3.4.10'
'3.8.06' | '3.5.07' | '3.4.11'
'3.8.07' | '3.5.08' | '3.4.12'
'3.8.08' | '3.5.09' | '3.4.13'
'3.8.09' | '3.5.10' | '3.4.14'
'3.8.10' | '3.5.11' | '3.5.01'
'3.8.11' | '3.5.12' | '3.5.02'
'3.8.12' | '3.5.13' | '3.5.03'
'3.8.13' | '3.6' | '3.5.04'
'3.8.14' | '3.6.1' | '3.5.05'
'3.8.15' | '3.6.2' | '3.5.06'
'3.8.16' | '3.7' | '3.5.07'
'3.8.17' | '3.7.1' | '3.5.08'
'3.8.18' | '3.7.2' | '3.5.09'
'3.8.19' | '3.7.3' | '3.5.10'
'3.8.20' | '3.7.4' | '3.5.11'
'3.8.21' | '3.7.5' | '3.5.12'
'3.8.22' | '3.7.6' | '3.5.13'
'3.8.23' | '3.7.7' | '3.8.01'
'3.8.24' | '3.7.8' | '3.8.02'
'3.8.25' | '3.8' | '3.8.03'
'3.8.26' | '3.8.01' | '3.8.04'
'3.8.27' | '3.8.02' | '3.8.05'
'3.8.28' | '3.8.03' | '3.8.06'
'3.8.29' | '3.8.04' | '3.8.07'
'3.8.30' | '3.8.05' | '3.8.08'
'3.8.31' | '3.8.06' | '3.8.09'
'3.8.32' | '3.8.07' | '3.8.10'
'3.8.33' | '3.8.08' | '3.8.11'
'3.8.34' | '3.8.09' | '3.8.12'
'3.8.35' | '3.8.10' | '3.8.13'
'3.8.36' | '3.8.11' | '3.8.14'
'3.8.37' | '3.8.12' | '3.8.15'
'3.8.38' | '3.8.13' | '3.8.16'
'3.8.39' | '3.8.14' | '3.8.17'
'3.10' | '3.8.15' | '3.8.18'
'3.10.1' | '3.8.16' | '3.8.19'
'3.10.2' | '3.8.17' | '3.8.20'
'3.11' | '3.8.18' | '3.8.21'
'3.11.1' | '3.8.19' | '3.8.22'
'3.11.2' | '3.8.20' | '3.8.23'
'3.11.3' | '3.8.21' | '3.8.24'
'3.12' | '3.8.22' | '3.8.25'
'3.12.1' | '3.8.23' | '3.8.26'
'3.12.2' | '3.8.24' | '3.8.27'
'3.12.3' | '3.8.25' | '3.8.28'
'3.12.4' | '3.8.26' | '3.8.29'
'3.13' | '3.8.27' | '3.8.30'
'3.13.1' | '3.8.28' | '3.8.31'
'3.13.2' | '3.8.29' | '3.8.32'
'3.13.3' | '3.8.30' | '3.8.33'
'3.13.4' | '3.8.31' | '3.8.34'
'3.13.5' | '3.8.32' | '3.8.35'
'3.13.6' | '3.8.33' | '3.8.36'
'3.13.7' | '3.8.34' | '3.8.37'
'3.13.8' | '3.8.35' | '3.8.38'
'3.13.9' | '3.8.36' | '3.8.39'
'3.13.10' | '3.8.37' | '3.13.10'
'3.13.11' | '3.8.38' | '3.13.11'
'3.13.12' | '3.8.39' | '3.13.12'


CREATE TABLE conta_a (codigo varchar)

insert into conta_a (codigo) values ('3.1');
insert into conta_a (codigo) values ('3.1.01');
insert into conta_a (codigo) values ('3.1.02');
insert into conta_a (codigo) values ('3.1.03');
insert into conta_a (codigo) values ('3.1.04');
insert into conta_a (codigo) values ('3.1.05');
insert into conta_a (codigo) values ('3.1.06');
insert into conta_a (codigo) values ('3.1.07');
insert into conta_a (codigo) values ('3.1.08');
insert into conta_a (codigo) values ('3.1.09');
insert into conta_a (codigo) values ('3.1.10');
insert into conta_a (codigo) values ('3.1.11');
insert into conta_a (codigo) values ('3.1.12');
insert into conta_a (codigo) values ('3.1.13');
insert into conta_a (codigo) values ('3.1.14');
insert into conta_a (codigo) values ('3.1.15');
insert into conta_a (codigo) values ('3.1.16');
insert into conta_a (codigo) values ('3.1.17');
insert into conta_a (codigo) values ('3.1.18');
insert into conta_a (codigo) values ('3.1.19');
insert into conta_a (codigo) values ('3.1.20');
insert into conta_a (codigo) values ('3.1.21');
insert into conta_a (codigo) values ('3.1.22');
insert into conta_a (codigo) values ('3.1.23');
insert into conta_a (codigo) values ('3.1.24');
insert into conta_a (codigo) values ('3.2');
insert into conta_a (codigo) values ('3.2.01');
insert into conta_a (codigo) values ('3.2.02');
insert into conta_a (codigo) values ('3.2.03');
insert into conta_a (codigo) values ('3.2.04');
insert into conta_a (codigo) values ('3.2.05');
insert into conta_a (codigo) values ('3.2.06');
insert into conta_a (codigo) values ('3.2.07');
insert into conta_a (codigo) values ('3.2.08');
insert into conta_a (codigo) values ('3.2.09');
insert into conta_a (codigo) values ('3.2.10');
insert into conta_a (codigo) values ('3.2.11');
insert into conta_a (codigo) values ('3.2.12');
insert into conta_a (codigo) values ('3.2.13');
insert into conta_a (codigo) values ('3.2.14');
insert into conta_a (codigo) values ('3.2.15');
insert into conta_a (codigo) values ('3.2.16');
insert into conta_a (codigo) values ('3.2.17');
insert into conta_a (codigo) values ('3.2.18');
insert into conta_a (codigo) values ('3.2.19');
insert into conta_a (codigo) values ('3.2.20');
insert into conta_a (codigo) values ('3.2.21');
insert into conta_a (codigo) values ('3.2.22');
insert into conta_a (codigo) values ('3.2.23');
insert into conta_a (codigo) values ('3.2.24');
insert into conta_a (codigo) values ('3.2.25');
insert into conta_a (codigo) values ('3.2.26');
insert into conta_a (codigo) values ('3.2.27');
insert into conta_a (codigo) values ('3.2.28');
insert into conta_a (codigo) values ('3.2.29');
insert into conta_a (codigo) values ('3.2.30');
insert into conta_a (codigo) values ('3.2.31');
insert into conta_a (codigo) values ('3.2.32');
insert into conta_a (codigo) values ('3.2.33');
insert into conta_a (codigo) values ('3.2.34');
insert into conta_a (codigo) values ('3.2.35');
insert into conta_a (codigo) values ('3.2.36');
insert into conta_a (codigo) values ('3.2.37');
insert into conta_a (codigo) values ('3.2.38');
insert into conta_a (codigo) values ('3.3');
insert into conta_a (codigo) values ('3.3.01');
insert into conta_a (codigo) values ('3.3.02');
insert into conta_a (codigo) values ('3.3.03');
insert into conta_a (codigo) values ('3.3.04');
insert into conta_a (codigo) values ('3.3.05');
insert into conta_a (codigo) values ('3.3.06');
insert into conta_a (codigo) values ('3.3.07');
insert into conta_a (codigo) values ('3.3.08');
insert into conta_a (codigo) values ('3.3.10');
insert into conta_a (codigo) values ('3.4');
insert into conta_a (codigo) values ('3.4.01');
insert into conta_a (codigo) values ('3.4.02');
insert into conta_a (codigo) values ('3.4.03');
insert into conta_a (codigo) values ('3.4.04');
insert into conta_a (codigo) values ('3.4.05');
insert into conta_a (codigo) values ('3.4.06');
insert into conta_a (codigo) values ('3.4.07');
insert into conta_a (codigo) values ('3.4.08');
insert into conta_a (codigo) values ('3.4.09');
insert into conta_a (codigo) values ('3.4.10');
insert into conta_a (codigo) values ('3.4.11');
insert into conta_a (codigo) values ('3.4.12');
insert into conta_a (codigo) values ('3.4.13');
insert into conta_a (codigo) values ('3.4.14');
insert into conta_a (codigo) values ('3.5');
insert into conta_a (codigo) values ('3.5.01');
insert into conta_a (codigo) values ('3.5.02');
insert into conta_a (codigo) values ('3.5.03');
insert into conta_a (codigo) values ('3.5.04');
insert into conta_a (codigo) values ('3.5.05');
insert into conta_a (codigo) values ('3.5.06');
insert into conta_a (codigo) values ('3.5.07');
insert into conta_a (codigo) values ('3.5.08');
insert into conta_a (codigo) values ('3.5.09');
insert into conta_a (codigo) values ('3.5.10');
insert into conta_a (codigo) values ('3.5.11');
insert into conta_a (codigo) values ('3.5.12');
insert into conta_a (codigo) values ('3.5.13');
insert into conta_a (codigo) values ('3.6');
insert into conta_a (codigo) values ('3.6.1');
insert into conta_a (codigo) values ('3.6.2');
insert into conta_a (codigo) values ('3.7');
insert into conta_a (codigo) values ('3.7.1');
insert into conta_a (codigo) values ('3.7.2');
insert into conta_a (codigo) values ('3.7.3');
insert into conta_a (codigo) values ('3.7.4');
insert into conta_a (codigo) values ('3.7.5');
insert into conta_a (codigo) values ('3.7.6');
insert into conta_a (codigo) values ('3.7.7');
insert into conta_a (codigo) values ('3.7.8');
insert into conta_a (codigo) values ('3.8');
insert into conta_a (codigo) values ('3.8.01');
insert into conta_a (codigo) values ('3.8.02');
insert into conta_a (codigo) values ('3.8.03');
insert into conta_a (codigo) values ('3.8.04');
insert into conta_a (codigo) values ('3.8.05');
insert into conta_a (codigo) values ('3.8.06');
insert into conta_a (codigo) values ('3.8.07');
insert into conta_a (codigo) values ('3.8.08');
insert into conta_a (codigo) values ('3.8.09');
insert into conta_a (codigo) values ('3.8.10');
insert into conta_a (codigo) values ('3.8.11');
insert into conta_a (codigo) values ('3.8.12');
insert into conta_a (codigo) values ('3.8.13');
insert into conta_a (codigo) values ('3.8.14');
insert into conta_a (codigo) values ('3.8.15');
insert into conta_a (codigo) values ('3.8.16');
insert into conta_a (codigo) values ('3.8.17');
insert into conta_a (codigo) values ('3.8.18');
insert into conta_a (codigo) values ('3.8.19');
insert into conta_a (codigo) values ('3.8.20');
insert into conta_a (codigo) values ('3.8.21');
insert into conta_a (codigo) values ('3.8.22');
insert into conta_a (codigo) values ('3.8.23');
insert into conta_a (codigo) values ('3.8.24');
insert into conta_a (codigo) values ('3.8.25');
insert into conta_a (codigo) values ('3.8.26');
insert into conta_a (codigo) values ('3.8.27');
insert into conta_a (codigo) values ('3.8.28');
insert into conta_a (codigo) values ('3.8.29');
insert into conta_a (codigo) values ('3.8.30');
insert into conta_a (codigo) values ('3.8.31');
insert into conta_a (codigo) values ('3.8.32');
insert into conta_a (codigo) values ('3.8.33');
insert into conta_a (codigo) values ('3.8.34');
insert into conta_a (codigo) values ('3.8.35');
insert into conta_a (codigo) values ('3.8.36');
insert into conta_a (codigo) values ('3.8.37');
insert into conta_a (codigo) values ('3.8.38');
insert into conta_a (codigo) values ('3.8.39');
insert into conta_a (codigo) values ('3.10');
insert into conta_a (codigo) values ('3.10.1');
insert into conta_a (codigo) values ('3.10.2');
insert into conta_a (codigo) values ('3.11');
insert into conta_a (codigo) values ('3.11.1');
insert into conta_a (codigo) values ('3.11.2');
insert into conta_a (codigo) values ('3.11.3');
insert into conta_a (codigo) values ('3.12');
insert into conta_a (codigo) values ('3.12.1');
insert into conta_a (codigo) values ('3.12.2');
insert into conta_a (codigo) values ('3.12.3');
insert into conta_a (codigo) values ('3.12.4');
insert into conta_a (codigo) values ('3.13');
insert into conta_a (codigo) values ('3.13.1');
insert into conta_a (codigo) values ('3.13.2');
insert into conta_a (codigo) values ('3.13.3');
insert into conta_a (codigo) values ('3.13.4');
insert into conta_a (codigo) values ('3.13.5');
insert into conta_a (codigo) values ('3.13.6');
insert into conta_a (codigo) values ('3.13.7');
insert into conta_a (codigo) values ('3.13.8');
insert into conta_a (codigo) values ('3.13.9');
insert into conta_a (codigo) values ('3.13.10');
insert into conta_a (codigo) values ('3.13.11');
insert into conta_a (codigo) values ('3.13.12');