Início / dba / Perguntas / 292451
Accepted
tucomax
Asked: 2021-05-29 23:59:11 +0800 CST

Como mesclar várias linhas em uma coluna com hierarquia Postgresql 12?

  • 772

Tenho uma tabela assim para salvar o catálogo de 90 serviços:

-------------------------------------------------------------------------------------
|  id  |  service     | subservice      | description                      | cost
-------------------------------------------------------------------------------------
| 2044 |   Tests      |   Tests         | Calcium                          | 50.00
| 1385 |   Cardiology |   Cardioversion | Electric Cardioversion programmed| 200.00
| 7000 |   Cardiology |  Ecocardiography| Chest Ultrasound                 | 100.00
-------------------------------------------------------------------------------------

Preciso alterar a estrutura da tabela para ter os três níveis (serviço, subserviço e descrição) na mesma coluna com id próprio e apontando para uma nova coluna com o número do nível. Ou seja, algo assim (observe que os id's são algo que eu inventei):

-------------------------------------------------------------------------------------
|  id  | description  | parent_id   |  cost
-------------------------------------------------------------------------------------
| 1    |   Tests      |       NULL  | 0.00
| 2    |   Tests      |         1   | 0.00
| 2044 |   Calcium    |         2   | 50.00
-------------------------------------------------------------------------------------

Estou trabalhando no Postgresql 12. Criei a nova coluna parent_id e estava tentando fazer esta sequência:

CREATE SEQUENCE seq_parent_id INCREMENT BY 1 START WITH 1 NO CYCLE;

ALTER TABLE catalog
ALTER COLUMN parent_id SET DEFAULT nextval('seq_parent_id')

Alguém pode por favor dar uma idéia aproximada de como transformar a estrutura da tabela?

postgresql cte

1 respostas

  1. Best Answer

    É um pouco longo, então os passos são:

    1. Mesa Antiga
    create table test (id int, service varchar, subservice varchar, description varchar, cost decimal(10,2));
    1. Nova tabela
    create table newtest(id int, description varchar, parent_id int, cost decimal(10,2));
    1. Crie uma sequência como você mencionou em sua pergunta
    CREATE SEQUENCE seq_parent_id INCREMENT BY 1 START WITH 1 NO CYCLE;
    1. Agora execute abaixo da consulta
    with cte(id, description, parent_id,cost) as 
(select nextval('seq_parent_id'), service, null::bigint,0::decimal  
from test group by 2
),
cte1(id, description, parent_id,cost) as 
(select nextval('seq_parent_id'), subservice, t2.id,0::decimal 
from test t1 
inner join cte t2 on t1.service=t2.description and t2.parent_id is null group by 2,3
)

insert into newtest

select * from cte
union all
select * from cte1
union all
select t1.id,t1.description,t3.id,t1.cost from test t1 
inner join cte t2 on t1.service=t2.description and t2.parent_id is null
inner join cte1 t3 on t1.subservice=t3.description and t3.parent_id=t2.id

    DEMO

    A explicação da consulta acima é demonstrada neste violino

    NOTA: Antes de executar a consulta, certifique-se de que o valor do contador não cruzará o ID já existente. Se as linhas recém-inseridas tiverem mais do que a contagem mínima existente, defina o início da sequência max(id)+1antes de executar esta consulta.

    • 1

relate perguntas