I haven't worked with postgres databases in over a decade, so I can't say with certainty how well DBD::Pg supports it, but my first thought would be to try $dbh->last_insert_id as a (more-or-less database-independent) alternative to trying to get the statement to return the ID when executed.

The reason you always get a result of 1 in $id is because, per the DBI docs, "For a non-SELECT statement, execute returns the number of rows affected, if known." INSERTing one row = 1 row affected.