Skip to content

Instantly share code, notes, and snippets.

@monsha
Last active March 13, 2021 00:44
Show Gist options
  • Save monsha/e15cc0ee5277a01df4be67722d4b99c4 to your computer and use it in GitHub Desktop.
Save monsha/e15cc0ee5277a01df4be67722d4b99c4 to your computer and use it in GitHub Desktop.
How to find corrupted records in postgresql
#Find the corrupted rows
DO $$
declare
curid INT := 0;
vcontent TEXT;
badid INT;
begin
FOR badid IN SELECT id FROM events LOOP
curid = curid + 1;
if curid % 100000 = 0 then
raise notice '% rows inspected', curid;
end if;
begin
SELECT message
INTO vcontent
FROM events where id = badid;
vcontent := substr(vcontent,100,2000);
exception
when others then
raise notice 'data for message % is corrupt', badid;
continue;
end;
end loop;
end;
$$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment