Change file encoding for PostgreSQL w/Perl

When you connect to PostgreSQL using DBI->connect(..., { pg_enable_utf8 => 1}) then the data used in all modifying DBI calls (SQL INSERT, UPDATE, DELETE, everywhere you use placeholders in queries etc) has to be encoded in Perl's internal encoding so that DBI itself can convert to the wire protocol correctly.

There are tons of ways how you can achieve that, and they all depend on how you read the file in the first place. The most basic one is if you use open (or one of the methods based directly on it like IO::File->open). You can then use Perl's I/O layers (see the open link above) and let Perl do that for you. Assuming your file is encoded in UTF-8 already you'll get away with:

open(my $fh, "<:encoding(UTF-8)", "filename");
while (my $line = <$fh>) {
  # process query
}

This is basically equivalent to opening the file without an encoding layer and converting manually using Encode::decode, e.g. like this:

open(my $fh, "<", "filename");
while (my $line = <$fh>) {
  $line = Encode::decode('UTF-8', $line);
  # process query
}

A lot of other modules that receive data from external sources and return it (think of HTTP downloads with LWP, for example) return values that have already been converted into Perl's internal encoding.

So what you have to do is:

• Figure out which encoding your file actually uses (try using iconv on the shell for that)
• Tell DBI to enable UTF-8
• Open the file with the correct encoding
• Read line(s), process query, repeat