Ordenação de uma cadeia de string Postgres

[Rosangela Westphal]

Boa noite pessoa, 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');

[Rosangela Westphal]

SELECT my_conta_a.*
FROM (SELECT conta_a.*
, CAST (STRING_TO_ARRAY(conta_a.codigo,'.') AS INT[]) AS my_ordem
FROM conta_a
) my_conta_a
ORDER BY my_conta_a.my_ordem

Funcionou assim.