Adding a Yoast Canonical when a new post is published in WordPress

Question

I need to add a Yoast canonical tag only when a post is published. If the post is already published and updated I do not want to update the canonical tag. I'm using add_post_meta but the function only works if I don't use the actual Yoast meta key. Is it possible there is some functionality of the Yoast plugin that's blocking me from adding this? Any ideas for a workaround?

add_action('new_to_publish', 'yoast_add_canonical_to_new_posts');
add_action('draft_to_publish', 'yoast_add_canonical_to_new_posts');
add_action('pending_to_publish', 'yoast_add_canonical_to_new_posts');

function yoast_add_canonical_to_new_posts( $post ) {

    // This does not add any new meta keys
    add_post_meta( (int) $post->ID, '_yoast_wpseo_canonical', (string) 'https://www.google.com' );
    
    // This adds the new meta key but it's not correct
    add_post_meta( (int) $post->ID, 'yoast_wpseo_canonical', (string) 'https://www.google.com' );
}

I've also tried using $_POST to insert the data without using the add_post_meta function but it still will not insert the data.

$_POST[ "yoast_wpseo_canonical" ] = 'https://www.google.com';

EDIT: I just tried inserting the value as yoast_wpseo_canonical and updating it to include the underscore with a $wpdb->update query and it now just removes the entry as soon as I update the value. I can change the value manually via phpMyAdmin but doing this through the functions.php file is causing serious issues.

I've also tried using $wpdb->query to insert the record directly and it never shows up. If I run the same exact query through phpMyAdmin it works.