65 lines
2.0 KiB
SQL
65 lines
2.0 KiB
SQL
-- Corrigir constraints da tabela configuracoes
|
|
|
|
-- 1. Remover a constraint incorreta (configuracoes_tipo_key)
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM pg_constraint
|
|
WHERE conname = 'configuracoes_tipo_key'
|
|
AND conrelid = 'public.configuracoes'::regclass
|
|
) THEN
|
|
ALTER TABLE public.configuracoes DROP CONSTRAINT configuracoes_tipo_key;
|
|
RAISE NOTICE 'Constraint configuracoes_tipo_key removida';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- 2. Garantir que existe constraint UNIQUE na coluna 'chave'
|
|
DO $$
|
|
BEGIN
|
|
-- Primeiro remover duplicados na coluna 'chave' (mantendo o mais recente)
|
|
DELETE FROM public.configuracoes
|
|
WHERE id IN (
|
|
SELECT id
|
|
FROM (
|
|
SELECT id, chave,
|
|
ROW_NUMBER() OVER (PARTITION BY chave ORDER BY updated_at DESC NULLS LAST, created_at DESC) as rn
|
|
FROM public.configuracoes
|
|
WHERE chave IS NOT NULL
|
|
) t
|
|
WHERE rn > 1
|
|
);
|
|
RAISE NOTICE 'Duplicados removidos da coluna chave';
|
|
|
|
-- Adicionar constraint UNIQUE em 'chave' se não existir
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_constraint
|
|
WHERE conname = 'configuracoes_chave_key'
|
|
AND conrelid = 'public.configuracoes'::regclass
|
|
) THEN
|
|
ALTER TABLE public.configuracoes ADD CONSTRAINT configuracoes_chave_key UNIQUE (chave);
|
|
RAISE NOTICE 'Constraint UNIQUE adicionada na coluna chave';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- 3. Verificar constraints existentes
|
|
SELECT
|
|
conname as constraint_name,
|
|
contype as constraint_type,
|
|
a.attname as column_name
|
|
FROM pg_constraint c
|
|
JOIN pg_attribute a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
|
|
WHERE c.conrelid = 'public.configuracoes'::regclass
|
|
ORDER BY conname;
|
|
|
|
-- 4. Verificar estrutura final
|
|
SELECT column_name, data_type, is_nullable
|
|
FROM information_schema.columns
|
|
WHERE table_schema = 'public'
|
|
AND table_name = 'configuracoes'
|
|
ORDER BY ordinal_position;
|
|
|
|
-- 5. Mostrar dados
|
|
SELECT id, chave, tipo, descricao, created_at, updated_at
|
|
FROM public.configuracoes
|
|
ORDER BY chave;
|