I ran into this recently myself with a project to insert E-mail messages into a PostgreSQL database. I solved it like so:

 
use utf8; 
use Encode; 
my $possibly_bad_utf8_data = get_data(); 
my $good_data = encode( "UTF-8", $possibly_bad_utf8_data );
[download]

The capitalization of the UTF-8 in the call to encode() is important, it tells encode to be "strict" about the UTF-8. It turns out use utf8; isn't 100% strict and hence can't be inserted into a strict mode PostgreSQL database.

Hope this helps.

Remarkable, thanks!

I had tried using Encode before, but missed the "UTF-8" bit apparently. It's working perfectly now.

It is NOT the capitalization that is needed. Encode is case-insensitive for the encoding. It is the hyphen that makes the difference, see this example:

use Encode qw(resolve_alias);

my @aliases = ('utf-8', 'UTF-8', 'utf8',);

for my $alias ( @aliases ) { 
my $canonical_name  = Encode::resolve_alias($alias);
print "$alias \t has canonical name $canonical_name\n";
}
[download]

This issue was recently discussed in London-pm mailing list. See the thread here (give special attention to Matt Lawrence's last posts):

http://london.pm.org/pipermail/london.pm/Week-of-Mon-20061127/thread.html#5758

It had to do with some weirdness of the bytea data type and the fine/fast solution was to use:

  $sth->bind_param($param_num, $bind_value,
                         { pg_type => DBD::Pg::PG_BYTEA });
[download]

Encode has functions for error detection and recovery.

A complete kludge, but perhaps munge the possibly-invalid data through MIME::Base64's encode_base64 and decode_base64 routines into/out of the database? Presuming your columns are large enough to handle the inflation that might be a work around until you figure out the right way to do it.