Dear list,
I have been banging my head against a problem for a few days now, and although I am making progress it is painfully
slow,and I am hoping that some one out there can steer me in a better way.
I've got a medium size database (7.4), about 64 gigs of data, about 1/3rd of which is in one table, which has ~32
millionrows (22 gigs when dumped). This largish table has about 20 different columns of varchar or text.
There are some records that have illegal characters in them, according to postgres 8.1.5, which imposes stricter
standardson UTF encoding.
I've been using copy to dump the big table to disk, then try to load it into my new table. When it fails, I use split
tobreak the file into managable chunks and then use ""vi" to find the offending line, then figure out the column. Then
Iuse something like:
create table bad_char_gids as select gid from parcels where position('Ñ' in s_street) > 0;
And so create a table with the ids of the bad records; and then use replace to either replace or eliminate the
offendingcharacters from that column. This example got 5001 records, but often it is one record in the whole DB will
havesome other offending character. I fix the problem in the loaddata as well, and continue.
The problem is that there are errors in quite a few of the columns (but only a few tens of thousands of records), and
theoffending characters are all quite different (wierd diacritics and characters, upper and lower case). And so this is
avery slow process.
Is there any way to get a list of records, even if done repeatedly for each column, that would let me find the
offendingrecords in 7.4 which have any invalid UTF chars? I am feeling stupid for not seeing one ... I can find any
individualbad character, but I want to find them all at once, if possible.
TIA,
Greg Williamson
DBA
GlobeXplorer LLC
