My system is running without any issues or difficulties. Now, I am trying to add a view in the same way as shown in the topic Show Corteza: Raw SQL Views In Reports. I can add the view without any difficulty, and it is working exactly as it should. However, I noticed that when I add any view to my system (not just this view, but others as well), when I attempt to restart my application, it hangs, and my server doesn’t come online at all.
I investigated the logs, and the following error appears: “could not start Corteza: could not upgrade primary store: pq: cannot alter the type of a column used by a view or rule.” How can I fix this issue?
View code:
CREATE OR REPLACE VIEW public.apontamento_ajustedeefetivos_relatorio1
AS
SELECT
‘1’::text AS id,
‘1’::text AS id2,
T0.id as “idtela”,
(T1.“values”->‘bplid’->>0) AS “Codigo_da_Filial”,
(T3.“values” → ‘Nome’->>0)as “Nome_da_Filial”,
CAST(T1.“values” ->> ‘data’ as DATE) as “Data”,
case
when extract(dow from CAST(T1.“values” ->> ‘data’ as DATE)) = 1 then ‘Seg’
when extract(dow from CAST(T1.“values” ->> ‘data’ as DATE)) = 2 then ‘Ter’
when extract(dow from CAST(T1.“values” ->> ‘data’ as DATE)) = 3 then ‘Qua’
when extract(dow from CAST(T1.“values” ->> ‘data’ as DATE)) = 4 then ‘Qui’
when extract(dow from CAST(T1.“values” ->> ‘data’ as DATE)) = 5 then ‘Sex’
when extract(dow from CAST(T1.“values” ->> ‘data’ as DATE)) = 6 then ‘Sab’
when extract(dow from CAST(T1.“values” ->> ‘data’ as DATE)) = 0 then ‘Dom’
else ‘Erro’
end AS “Dia_da_Semana”,
(T1.“values” → ‘turno’->>0) AS “Turno”,
(T1.“values” → ‘serviconame’->>0) AS “Servico”,
(T2.“values” → ‘cardcode’->>0) AS “Codigo_do_Cliente”,
(T2.“values” → ‘cardname’->>0) AS “Nome_do_Cliente”,
to_number((T2.“values” → ‘quantidadeajustada’->>0), ‘000000.0000000’) AS “Quantidade”
FROM ajusteefetivosinformacoes T0
left join ajusteefetivoshead T1 on CAST(T1.“values” ->> ‘data’ as DATE) >= CAST(T0.“values” ->> ‘data_inicio’ as DATE) and CAST(T1.“values” ->> ‘data’ as DATE) <= CAST(T0.“values” ->> ‘data_fim’ as DATE) and (T1.values ->‘responsavel’->>0) = text(T0.created_by)
lefT join ajusteefetivosline T2 ON text(T1.id) = (T2.“values” → ‘parentkey’->>0)
left join compose_record T3 on T3.rel_namespace = 339416282241695746 and T3.rel_module = 362638317373620228 and (T1.values ->‘filial’->>0) = text(T3.id)
where
T1.id is not null
;