How do I validate all my constraints in postgresql?
Well, if you need a way to check if all the foreign keys in your table are valid, this might help ( it just validates all foreign keys in your schema )
do $$
declare r record;
BEGIN
FOR r IN (
SELECT FORMAT(
'ALTER TABLE %I VALIDATE CONSTRAINT %I;',
tc.table_name,
tc.constraint_name
) AS x
FROM information_schema.table_constraints AS tc
JOIN information_schema.tables t ON t.table_name = tc.table_name and t.table_type = 'BASE TABLE'
JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY'
AND tc.constraint_schema = 'public'
)
LOOP
EXECUTE (r.x);
END LOOP;
END;
$$;
The solution proposed By Evan Carroll was not working for me.
I had to adapt it to mark before all constraints as not valid.
do $$
declare r record;
BEGIN
FOR r IN (
SELECT FORMAT(
'UPDATE pg_constraint SET convalidated=false WHERE conname = ''%I''; ALTER TABLE %I VALIDATE CONSTRAINT %I;',
tc.constraint_name,
tc.table_name,
tc.constraint_name
) AS x
FROM information_schema.table_constraints AS tc
JOIN information_schema.tables t ON t.table_name = tc.table_name and t.table_type = 'BASE TABLE'
JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY'
AND tc.constraint_schema = 'public'
)
LOOP
EXECUTE (r.x);
END LOOP;
END;
$$;
This will work on all constraints safely.,
SELECT FORMAT(
'ALTER TABLE %I.%I.%I VALIDATE CONSTRAINT %I;',
current_database(),
nsp.nspname,
cls.relname,
con.conname
)
FROM pg_constraint AS con
JOIN pg_class AS cls
ON con.conrelid = cls.oid
JOIN pg_namespace AS nsp
ON cls.relnamespace = nsp.oid
WHERE convalidated IS FALSE
-- or delete it for all constraints in all schemas
AND nsp.nspname = 'mySchema';
You can either save that to a file and Q/A it or execute it all at once if using psql
with \gexec
.