DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=DJXyMRnp7ckiK90FXdnk2nAop0G2bTlVCENB+8xr5+PZcEEj/OYmLhd/n512Vo+aH+
	jj4MgxDgy5qbGN3Abw+NtDP+HeeU/KsKRy8ZBEp+bVrfr+uNJdvGUuh7yKBB3q7tZeat
	91uLL+IteJgSB8Pd9+1KDrFhofTlLllC0J+04=
Message-ID: <57a815bf0809190637h49abd565x108ce52bc92a9d0a@mail.gmail.com>
Date: Fri, 19 Sep 2008 23:37:03 +1000
From: "Mikel Lindsaar" <raasdnil@gmail.com>
To: "Tore Halset" <halset@pvv.ntnu.no>, pgsql-admin@postgresql.org
Subject: Re: unable to restore 8.2.5
In-Reply-To: <35E76852-E52D-4BE7-9931-7B1AE6805248@pvv.ntnu.no>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <35E76852-E52D-4BE7-9931-7B1AE6805248@pvv.ntnu.no>

On Fri, Sep 19, 2008 at 6:29 PM, Tore Halset <halset@pvv.ntnu.no> wrote:
> Looks like I have managed to insert an illegal character into the main
> system that does not conform to UTF-8. Anything I can and should do to work
> around this issue?

I have had the same problem previously and after a lot of help from
Tom Lane basically came up to the following...

You need to basically dump your table out (or a subset containing the
row ID and column that would have the bad data) in plain text and then
parse it with a script to detect invalid UTF-8 sequences, then find
what rows the bad data is in and go and fix it.

It is either that or you drop the data inserting some other character.
 But this has obvious drawbacks.

I wrote a short ruby script that goes through a dumped file line by
line and puts each line through Iconv to parse it from UTF-8 to UTF-8,
if it fails it dumps the offending line to a log file.

A ruby script that would just print the offending row would go
something like this:

require 'iconv'
File.read(ARGV[0]).each do |line|
  begin
    Iconv.iconv('UTF-8', 'UTF-8', line)
  rescue
    puts "Failed: #{line}"
  end
end

Save that in a file (find_invalid_utf8.rb) then run it with:

$ ruby find_invalid_utf8.rb my_dumped_table.csv

It's not pretty, and just dumps the raw output to the screen, but it
might do for you.

-- 
http://lindsaar.net/
Rails, RSpec and Life blog....