Character problems with UTF8 PostgreSQL

[MrCBofBCinTX]

I just switched my database from SQL_ASCII to UTF8 (and changed some table names, too) by using pgdump and editing sql file until I could reload a new database without any character errors.

I found some insertion problems. I already use HTML::Entities.
I added Encode.
Most problems solved, but I am still getting nonsense characters for things like trademark symbol and that r in a circle and degree signs, etc.

I added some substitutions, which helped most of problems, but not all.

I think I am not getting this quite right.

Should I do something like HTML::Entities::encode followed right away by a decode? Something else? Or is a list of substitutions the only (ugly) answer?

 

[MrCBofBCinTX]

For DBI, using { pg_enable_utf8 => 1 }
does most of what's needed. (I still need to work on solving sites that send characters in wrong encoding on a utf8 page), but finding this really helps

 

[MrCBofBCinTX]

I seem to have things ok for he most part now.

Using the following substitutions seems necessary:

$iproduct_description = $query->param( $name );
		$iproduct_description =~ s/"//g;
		$iproduct_description =~ s/?//g; # Forgot what this was, copied it
		$iproduct_description =~ s/?/ Deg./g; # This is a degree symbol
		$iproduct_description =~ s/Ã//g;
		$iproduct_description =~ s/‚//g;
		$iproduct_description =~ s/Â//g;
		$iproduct_description =~ s/®//g;
		$iproduct_description =~ s/Â//g;
		$iproduct_description =~ s/®//g;
		$iproduct_description =~ s/™//g;
		$iproduct_description = HTML::Entities::decode($iproduct_description);
		$iproduct_description = encode("utf8", $iproduct_description);