Why would this query fail when there obviously is no duplicate in that table?

It doesn't fail on querying from the table, but on inserting. And because it fails, there are no duplicates in the table.

I don't understand your code, it's full of variables that I haven't seen the definition of, neither do we know anything about the table, the queries or the constraints on the table, but if the database complains that you're trying to add a key that's already there, you can be pretty sure that that's what you're doing wrong.

What is the constraint defined by 'mlg_addr_uniq_key'? Is the key in your table 'mlg_addr_id'?

It looks like you're trying to insert a row with an empty id (possibly that's what the uninitialized value warning was about), and probably you've run this same script before and successfully inserted a row with an empty id. (That an empty ID is allowed might be a separate problem.)

Try something like this (or something along those lines):

SELECT * FROM mlg_addr WHERE mlg_addr_id = ''
[download]

and see if you get any hits. If you do, that's the problem.

UPDATE: After seeing the "gruesome details", I'm way off base.

Thanks for turning this one over for a moment.

=# SELECT * FROM mlg_addr WHERE mlg_addr_id = '';
ERROR:  invalid input syntax for integer: ""
=# SELECT * FROM mlg_addr WHERE mlg_addr_id IS NULL;
 mlg_addr_id | address1 | address2 | city | state | zipcode5 | zipcode
+4 | country
-------------+----------+----------+------+-------+----------+--------
+--+---------
(0 rows)
[download]

if( $lal && $lol ) { $life++; }

So here's the offending key:

"mlg_addr_uniq_key" UNIQUE, btree (country, zipcode5, zipcode4, addres
+s1, address2)
[download]

The query you're using after the fact isn't checking against the key. You're looking at address1, city, and state... when address1 is the only field there that's part of the key. Try looking at just the fields that make up the key. I'd start by just looking for things that match or are like your address1, then paring down from that if necessary.

Use of uninitialized value in concatenation (.) or string at parse_vot
+er_list.pl line 251
[download]

if(!defined($mlg_addr_id) && length($fields[20]) == 2){
    $sth_insert_mlg_addr->execute(@fields[17..23])
        or warn "While inserting new mailing address |$fields[17]|; |$
+fields[19]| |$fields[20]|, with id |$mlg_addr_id|: " . $dbh->errstr;
[download]

I'm not sure I can comment coherently on the rest of your details, but if you haven't heard about "ON DUPLICATE KEY UPDATE ...", you might want to look that up, just in case it's relevant.

First think I do with code I don't understand is reformat it ;) So just in case its usefull to you i've included the reformatted code. All I really did was use slices so the arrays are readable and then edited the error messages to look similar. Once done it was fairly obvious that the error was on line #14 and that its using $mlg_addr_id which you distinctly specified shouldn't be defined. BTW i don't you sequnce of duplicate checks at all, it might make since but its hard to tell from this small sample of code.

$sth_mlg_addr_duplicate_check->execute(@fields[17,18,19,20,21,22,23])
      or warn "While checking for duplicate mailing address "
            . "$fields[17]; $fields[19] $fields[20]: "
            . $dbh->errstr;
              
($mlg_addr_id) = $sth_mlg_addr_duplicate_check->fetchrow_array();

my $mlg_id;
if(!defined($mlg_addr_id) && length($fields[20]) == 2) {

   #insert 
   $sth_insert_mlg_addr->execute(@fields[17,18,19,20,21,22,23])
      or warn "While inserting new mailing address "
            . "|$fields[17]|; |$fields[19]| |$fields[20]|, with id |$m
+lg_addr_id|: "
            . $dbh->errstr;

   #check duplicate
   $sth_mlg_addr_duplicate_check->execute(@fields[17,18,19,20,21,22,23
+])
      or warn "While checking for duplicate mailing address "
            . "|$fields[17]|; |$fields[19]| |$fields[20]|: "
            . $dbh->errstr;
            
   ($mlg_addr_id) = $sth_mlg_addr_duplicate_check->fetchrow_array();
   $mlg_id = $mlg_addr_id;
} else {
   $mlg_id = $mlg_addr_id;
}
[download]